<%=ray%> online query analzyer for access 2000

[jason]

Hi Ray...a while ago you explained an elegant solution to enable me to
CREATE and EDIT existing tables and queries inside my online access 2000
database.... could you provide refresher links on this or possibly some
starter code?

I am just so sick of the syncronization problems that come into play when
you adjust underlying tables and queries offline and then have to overwrite
the live database?

Many thanks Jason

 

[Ray at]

This is probably the link that I posted. (Be aware that this page will
display an unprofessional word on your monitor, so if you work at a church
or something, make sure you don't offend anyone.)

http://rtfm.atrax.co.uk/infinitemonkeys/articles/asp/908.asp

And here is something that I posted to another forum a few months back.

<quote>

You can modify you Acccess db with sql commands if you can't take your
site off line. You can just make yourself a password protected SQL
command page like so:


<form name="frmDatabase" method="post" action="sql.asp">
Enter SQL Command Below<BR>
<textarea name="txtSQL" style="width: 550px; height: 100px;"></textarea>
<input name="cmdSubmit" type="submit" value="Submit">
</form>




Then post that to a page like:

sSQL = Request.Form("txtSQL")
''create your connection and connect here
YourConnectionjADO.Execute sSQL



You can pass things like "ALTER TABLE [TableName] ADD COLUMN
NameOfYourNewColumn text(100)"

Just make sure you protect you pages heavily! If unauthorized person
gets in there, he could delete all your data, drop your tables, etc.


</quote>

Ray at work

 

[jason]

Thanks Ray...although the only curve balls with his examples are that they
are in JScript and he seems to use a Windows Script Component which will not
be avialable to me to use on my web host......
I don't suppose you have come across any pure asp examples...?

Thanks again
Jason

This is probably the link that I posted. (Be aware that this page will
display an unprofessional word on your monitor, so if you work at a church
or something, make sure you don't offend anyone.)

http://rtfm.atrax.co.uk/infinitemonkeys/articles/asp/908.asp

And here is something that I posted to another forum a few months back.

<quote>

You can modify you Acccess db with sql commands if you can't take your
site off line. You can just make yourself a password protected SQL
command page like so:


<form name="frmDatabase" method="post" action="sql.asp">
Enter SQL Command Below<BR>
<textarea name="txtSQL" style="width: 550px; height: 100px;"></textarea>
<input name="cmdSubmit" type="submit" value="Submit">
</form>




Then post that to a page like:

sSQL = Request.Form("txtSQL")
''create your connection and connect here
YourConnectionjADO.Execute sSQL



You can pass things like "ALTER TABLE [TableName] ADD COLUMN
NameOfYourNewColumn text(100)"

Just make sure you protect you pages heavily! If unauthorized person
gets in there, he could delete all your data, drop your tables, etc.


</quote>

Ray at work

Hi Ray...a while ago you explained an elegant solution to enable me to
CREATE and EDIT existing tables and queries inside my online access 2000
database.... could you provide refresher links on this or possibly some
starter code?

I am just so sick of the syncronization problems that come into play when
you adjust underlying tables and queries offline and then have to overwrite
the live database?

Many thanks Jason

 

[jason]

I am also looking at this code snippet -

http://www.codetoad.com/asp_query_displayer.asp

This is probably the link that I posted. (Be aware that this page will
display an unprofessional word on your monitor, so if you work at a church
or something, make sure you don't offend anyone.)

http://rtfm.atrax.co.uk/infinitemonkeys/articles/asp/908.asp

And here is something that I posted to another forum a few months back.

<quote>

You can modify you Acccess db with sql commands if you can't take your
site off line. You can just make yourself a password protected SQL
command page like so:


<form name="frmDatabase" method="post" action="sql.asp">
Enter SQL Command Below<BR>
<textarea name="txtSQL" style="width: 550px; height: 100px;"></textarea>
<input name="cmdSubmit" type="submit" value="Submit">
</form>




Then post that to a page like:

sSQL = Request.Form("txtSQL")
''create your connection and connect here
YourConnectionjADO.Execute sSQL



You can pass things like "ALTER TABLE [TableName] ADD COLUMN
NameOfYourNewColumn text(100)"

Just make sure you protect you pages heavily! If unauthorized person
gets in there, he could delete all your data, drop your tables, etc.


</quote>

Ray at work

Hi Ray...a while ago you explained an elegant solution to enable me to
CREATE and EDIT existing tables and queries inside my online access 2000
database.... could you provide refresher links on this or possibly some
starter code?

I am just so sick of the syncronization problems that come into play when
you adjust underlying tables and queries offline and then have to overwrite
the live database?

Many thanks Jason

 

[jason]

Hey Ray - I got the code toad working example up and running and it works
great for simple selects but how does one syntaxically exec a stored
query...do you use the same syntax as asp or do you assume you are inside
the access database:

For instance,

SQL = "EXEC qry_ListingPriceChanges_Condition @LID=Null, @ConID=" & ConID &
" ,@ActiveID=" & ActiveID 'Response.Write SQL 'Response.END set rs =
cnn.execute(SQL

If I keep the above simple and attempt to run it via the query analzyer:

qry_ListingPriceChanges_Condition @LID=Null,@ConID=Null,@ActiveID=Null

....I get 0 records affected - but is this the way I should be writing it or
like this:

EXEC qry_ListingPriceChanges_Condition @LID=Null,@ConID=Null,@ActiveID=Null

Either way seems to bring 0 records....any advice?

Thanks
Jason

I am also looking at this code snippet -

http://www.codetoad.com/asp_query_displayer.asp

This is probably the link that I posted. (Be aware that this page will
display an unprofessional word on your monitor, so if you work at a church
or something, make sure you don't offend anyone.)

http://rtfm.atrax.co.uk/infinitemonkeys/articles/asp/908.asp

And here is something that I posted to another forum a few months back.

<quote>

You can modify you Acccess db with sql commands if you can't take your
site off line. You can just make yourself a password protected SQL
command page like so:


<form name="frmDatabase" method="post" action="sql.asp">
Enter SQL Command Below<BR>
<textarea name="txtSQL" style="width: 550px; height: 100px;"></textarea>
<input name="cmdSubmit" type="submit" value="Submit">
</form>




Then post that to a page like:

sSQL = Request.Form("txtSQL")
''create your connection and connect here
YourConnectionjADO.Execute sSQL



You can pass things like "ALTER TABLE [TableName] ADD COLUMN
NameOfYourNewColumn text(100)"

Just make sure you protect you pages heavily! If unauthorized person
gets in there, he could delete all your data, drop your tables, etc.


</quote>

Ray at work

Hi Ray...a while ago you explained an elegant solution to enable me to
CREATE and EDIT existing tables and queries inside my online access 2000
database.... could you provide refresher links on this or possibly some
starter code?

I am just so sick of the syncronization problems that come into play when
you adjust underlying tables and queries offline and then have to overwrite
the live database?

Many thanks Jason

 

[Aaron Bertrand [MVP]]

There are some tools listed here, several of them are also valid for Access,
and of those, most are free (or relatively cheap).

http://www.aspfaq.com/2442

 

[Ray at]

I'm sorry. I don't understand exactly what you're asking. Are you trying
to get the "0 records affected" message back in your page, or are you saying
that you're not sure why there were ZERO records affected as opposed to >0?

Ray at work

 

[jason]

Excuse confusion...

The query analzyer from codetoad works great with simple select and delete
queries.

However, when it comes to ACTION queries I am at a loss as to how to CREATE
eg:

1. A NEW QUERY (eg qry_insert_NewListing)
2. A NEW TABLE (eg: tblYachts)
3. ...and finally, I am unclear as to how to pass parameters to the query in
the analzyer window as this does not seem to be valid:
qry_ListingPriceChanges_Condition @LID=Null,@ConID=Null,@ActiveID=Null

Thanks
Jason

ps: Is there a really good straightfoward site that deals with action
queries inside Access 2000? A google search is not bringing
up great results....

 

[jason]

Thanks - those links are helpful.

There are some tools listed here, several of them are also valid for Access,
and of those, most are free (or relatively cheap).

http://www.aspfaq.com/2442

 

[Ray at]

Excuse confusion...

The query analzyer from codetoad works great with simple select and delete
queries.

However, when it comes to ACTION queries I am at a loss as to how to CREATE
eg:

1. A NEW QUERY (eg qry_insert_NewListing)

Yeah, I'm not sure how you create queries with SQL in an Access database.
Where's Bob Barrows?

2. A NEW TABLE (eg: tblYachts)

This thing that you're using, does it just execute whatever adhoc query you
throw at it? That's all that I've used. And if it's like that, you'd do
something like:

CREATE TABLE tblYachts1 (YachtID AUTOINCREMENT PRIMARY KEY, YachtName
TEXT(255), YachtPrice NUMBER)

3. ...and finally, I am unclear as to how to pass parameters to the query in
the analzyer window as this does not seem to be valid:
qry_ListingPriceChanges_Condition @LID=Null,@ConID=Null,@ActiveID=Null

Here's a decent link.
http://www.asp101.com/samples/viewasp.asp?file=storedqueries.asp

Ray at work

 

[Bob Barrows]

Yeah, I'm not sure how you create queries with SQL in an Access
database. Where's Bob Barrows?

You called?

Jet 4.0 supports the use of CREATE VIEW and CREATE PROCEDURE statements. I
have not been able to get these commands to work using the Access Query
Builder. However, they seem to work fine when executed via ADO, so your tool
may be able to successfully run them. Here's an example:

CREATE PROCEDURE qTest ([parm1] character,[parm2] character) AS INSERT INTO
sometable(col1, col2)VALUES ([parm1],[parm2])

For more details see the Microsoft JetSQL help section in the Access online
help

HTH,
Bob Barrows

 

[Ray at]

He's alright, that guy. :]

Ray at work

 

[jason]

Definatelyi! Hes the guy you call in when all hope is lost!

- Jason

He's alright, that guy. :]

Ray at work

Jet 4.0 supports the use of CREATE VIEW and CREATE PROCEDURE statements. I
have not been able to get these commands to work using the Access Query
Builder. However, they seem to work fine when executed via ADO, so your tool
may be able to successfully run them. Here's an example:

CREATE PROCEDURE qTest ([parm1] character,[parm2] character) AS INSERT INTO
sometable(col1, col2)VALUES ([parm1],[parm2])

 

[jason]

Thanks Bob!! I was starting to give up hope there for a moment!

Just with regards definiing characters (and, I will chk online help too - I
assume you mean Microsoft online help?) if I was going to create a simple
query like this:

qry_insert_model
PARAMETERS pModel Text ( 255 );
INSERT INTO tblModel ( Model )
VALUES (pModel);

Would this put me on the right track:

CREATE PROCEDURE qry_insert_model
([pModel] character AS INSERT INTO
tblModel (Model)VALUES ([pModel])

....you see, I am concerned about the data type settings......is 'character'
the same as TEXT(255)?

One last thing: I have used ADO to navigate my online tables:

Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")

objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" &
Server.MapPath("../../database/listings.mdb")

Dim objTable, objColumn
For Each objTable in objADOXDatabase.Tables
If objTable.Type = "TABLE" then
Response.Write "<font color=red><STRONG>" & objTable.Name &
"</STRONG></font><br>"

For Each objColumn in objTable.Columns
Response.Write "&nbsp;&nbsp;&nbsp;" & objColumn.Name & "<br>"
Next

Response.Write "<p>"
End If
Next

Set objADOXDatabase = Nothing

.............But how does one traverse ones queries?

Appreciated!!!
- Jason

- Jason

Yeah, I'm not sure how you create queries with SQL in an Access
database. Where's Bob Barrows?

You called?

Jet 4.0 supports the use of CREATE VIEW and CREATE PROCEDURE statements. I
have not been able to get these commands to work using the Access Query
Builder. However, they seem to work fine when executed via ADO, so your tool
may be able to successfully run them. Here's an example:

CREATE PROCEDURE qTest ([parm1] character,[parm2] character) AS INSERT INTO
sometable(col1, col2)VALUES ([parm1],[parm2])

For more details see the Microsoft JetSQL help section in the Access online
help

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[jason]

Hey Chris - I just posted that same question - I think - to Bob in my last
reply....

I am a bit unclear about this....I have noted that Adox allows me to
traverse my tables....are you saying there is no way for me to traverse my
queries....

I am kind of consolidated on Access 2000 and suspect a whole host of
problems if I try to convert the database from 2000 as I am about to go live
with a pretty big application (I know, I know, sql server or msde should be
my choice).....

Could you give me further advice....I posted an adox sample in my last reply
to Box...;

Cheers
Jason

Excuse confusion...

The query analzyer from codetoad works great with simple select and
delete queries.

However, when it comes to ACTION queries I am at a loss as to how to
CREATE eg:

1. A NEW QUERY (eg qry_insert_NewListing)

Yeah, I'm not sure how you create queries with SQL in an Access
database. Where's Bob Barrows?

You called?

Jet 4.0 supports the use of CREATE VIEW and CREATE PROCEDURE statements. I
have not been able to get these commands to work using the Access Query
Builder. However, they seem to work fine when executed via ADO, so your tool
may be able to successfully run them. Here's an example:

CREATE PROCEDURE qTest ([parm1] character,[parm2] character) AS INSERT INTO
sometable(col1, col2)VALUES ([parm1],[parm2])

For more details see the Microsoft JetSQL help section in the Access online
help

FYI, you will not be able to "see" the queries in Access2K, but they are
there. This has been corrected in Access2002.

-Chris Hohmann

 

[Chris Hohmann]

Yeah, I'm not sure how you create queries with SQL in an Access
database. Where's Bob Barrows?

You called?

Jet 4.0 supports the use of CREATE VIEW and CREATE PROCEDURE statements. I
have not been able to get these commands to work using the Access Query
Builder. However, they seem to work fine when executed via ADO, so your tool
may be able to successfully run them. Here's an example:

CREATE PROCEDURE qTest ([parm1] character,[parm2] character) AS INSERT INTO
sometable(col1, col2)VALUES ([parm1],[parm2])

For more details see the Microsoft JetSQL help section in the Access online
help

FYI, you will not be able to "see" the queries in Access2K, but they are
there. This has been corrected in Access2002.

-Chris Hohmann

 

[Chris Hohmann]

Hey Chris - I just posted that same question - I think - to Bob in my last
reply....

I am a bit unclear about this....I have noted that Adox allows me to
traverse my tables....are you saying there is no way for me to traverse my
queries....

I am kind of consolidated on Access 2000 and suspect a whole host of
problems if I try to convert the database from 2000 as I am about to go live
with a pretty big application (I know, I know, sql server or msde should be
my choice).....

Could you give me further advice....I posted an adox sample in my last reply
to Box...;

I actually meant if you downloaded your database locally to your machine
and opened it, the queries you create through DDL would not appear in
the queries tab. Enumerating though ADOX should be fine. The disconnect
occurs because the Access application runs in DAO and DAO!=ADO. Enough
abbreviations for ya?!

-Chris Hohmann

 

[Bob Barrows]

Thanks Bob!! I was starting to give up hope there for a moment!

Just with regards definiing characters (and, I will chk online help
too - I assume you mean Microsoft online help?)

No, I meant Access online help.

if I was going to
create a simple query like this:

qry_insert_model
PARAMETERS pModel Text ( 255 );
INSERT INTO tblModel ( Model )
VALUES (pModel);

Would this put me on the right track:

CREATE PROCEDURE qry_insert_model
([pModel] character AS INSERT INTO
tblModel (Model)VALUES ([pModel])
Yes


...you see, I am concerned about the data type settings......is
'character' the same as TEXT(255)?

That's what you will find in the JetSQL section of the Access online help

One last thing: I have used ADO to navigate my online tables:

<pedantic_mode>
No, I think you mean you've used ADOX

Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")

objADOXDatabase.ActiveConnection =

"Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" &
Server.MapPath("../../database/listings.mdb")

Dim objTable, objColumn
For Each objTable in objADOXDatabase.Tables

............But how does one traverse ones queries?

You loop through either the Views or the Procedures collection, depending on
whether the saved query is a View (select statement with no parameters) or a
Procedure (all others).

HTH,
Bob Barrows

 

[jason]

Your are right! I just checked......

I find this a big nuisance as I may be testing the CREATE query locallly
before implementing via my online query analzyer on my live site.....it thus
makes things very difficult if I cannot eyeball it in the queries pane and
open in edit mode....

Is there anyway around this...some kind of a patch or option mode I could
turn on?

Thanks
Jason

 

[jason]

Thanks!

Thanks Bob!! I was starting to give up hope there for a moment!

Just with regards definiing characters (and, I will chk online help
too - I assume you mean Microsoft online help?)

No, I meant Access online help.

if I was going to
create a simple query like this:

qry_insert_model
PARAMETERS pModel Text ( 255 );
INSERT INTO tblModel ( Model )
VALUES (pModel);

Would this put me on the right track:

CREATE PROCEDURE qry_insert_model
([pModel] character AS INSERT INTO
tblModel (Model)VALUES ([pModel])
Yes


...you see, I am concerned about the data type settings......is
'character' the same as TEXT(255)?

That's what you will find in the JetSQL section of the Access online help

One last thing: I have used ADO to navigate my online tables:

<pedantic_mode>
No, I think you mean you've used ADOX

Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")

objADOXDatabase.ActiveConnection =

"Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" &
Server.MapPath("../../database/listings.mdb")

Dim objTable, objColumn
For Each objTable in objADOXDatabase.Tables

............But how does one traverse ones queries?

You loop through either the Views or the Procedures collection, depending on
whether the saved query is a View (select statement with no parameters) or a
Procedure (all others).

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"