<%=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/infinitemonk...es/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" <ja***@catamaranco.com> wrote in message
news:ek*************@tk2msftngp13.phx.gbl...

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]

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
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:#S**************@tk2msftngp13.phx.gbl...

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/infinitemonk...es/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" <ja***@catamaranco.com> wrote in message
news:ek*************@tk2msftngp13.phx.gbl...

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
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:#S**************@tk2msftngp13.phx.gbl...

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/infinitemonk...es/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" <ja***@catamaranco.com> wrote in message
news:ek*************@tk2msftngp13.phx.gbl...

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
"jason" <ja***@catamaranco.com> wrote in message
news:O3**************@TK2MSFTNGP09.phx.gbl...

I am also looking at this code snippet -

http://www.codetoad.com/asp_query_displayer.asp
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:#S**************@tk2msftngp13.phx.gbl...

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/infinitemonk...es/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" <ja***@catamaranco.com> wrote in message
news:ek*************@tk2msftngp13.phx.gbl...

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

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"jason" <ja***@catamaranco.com> wrote in message
news:ek*************@tk2msftngp13.phx.gbl...

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]

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" <ja***@catamaranco.com> wrote in message
news:eE**************@TK2MSFTNGP11.phx.gbl...

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

[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....
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:OD*************@tk2msftngp13.phx.gbl...

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" <ja***@catamaranco.com> wrote in message
news:eE**************@TK2MSFTNGP11.phx.gbl...

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

[jason]

Thanks - those links are helpful.
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:uA*************@TK2MSFTNGP11.phx.gbl...

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

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"jason" <ja***@catamaranco.com> wrote in message
news:ek*************@tk2msftngp13.phx.gbl...

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]

"jason" <ja***@catamaranco.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...

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/viewas...dqueries%2Easp

Ray at work

[Bob Barrows]

Ray at <%=sLocation%> wrote:

"jason" <ja***@catamaranco.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...

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

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.

[Ray at]

He's alright, that guy. :]

Ray at work

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eI*************@TK2MSFTNGP11.phx.gbl...

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]

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

- Jason

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:uZ**************@TK2MSFTNGP11.phx.gbl...

He's alright, that guy. :]

Ray at work

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eI*************@TK2MSFTNGP11.phx.gbl...

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])

[Chris Hohmann]

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eI*************@TK2MSFTNGP11.phx.gbl...

Ray at <%=sLocation%> wrote:

"jason" <ja***@catamaranco.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...

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

[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
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eI*************@TK2MSFTNGP11.phx.gbl...

Ray at <%=sLocation%> wrote:

"jason" <ja***@catamaranco.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...

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

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
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:OE**************@TK2MSFTNGP12.phx.gbl...

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eI*************@TK2MSFTNGP11.phx.gbl...

Ray at <%=sLocation%> wrote:

"jason" <ja***@catamaranco.com> wrote in message
news:um**************@TK2MSFTNGP12.phx.gbl...
> 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]

"jason" <ja***@catamaranco.com> wrote in message
news:eQ**************@TK2MSFTNGP09.phx.gbl...

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]

jason wrote:

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
</pedantic_mode>

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"

[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
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...

"jason" <ja***@catamaranco.com> wrote in message
news:eQ**************@TK2MSFTNGP09.phx.gbl...

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

[jason]

Thanks!
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:#M**************@TK2MSFTNGP12.phx.gbl...

jason wrote:

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
</pedantic_mode>

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"

[Chris Hohmann]

"jason" <ja***@catamaranco.com> wrote in message
news:eK**************@TK2MSFTNGP11.phx.gbl...

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
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...

"jason" <ja***@catamaranco.com> wrote in message
news:eQ**************@TK2MSFTNGP09.phx.gbl...

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

Sorry, I don't believe there's any easy way around this limitation. Here
are some things you could try:
1. Someone in the m.p.access.* groups recommended creating a VBA (Visual
Basic for Applications) to check for DDL created objects at startup and
created visible clone thereof.
2. Muck around with the MSys tables. I suspect that the "hidden" nature
of the DDL created objects are tied to the Flags field of the
MSysObjects table. Unfortunately, their is no official documentation for
the MSys tables, so this is complete conjecture on my part.

WARNING!!!! DO NOT muck around with the MSys tables in a production
database. You are likely to render your database completely useless
during the course of testing. I can not recommended strongly enough that
you avoid editing the MSys tables unless it is absolutely necessary.

HTH
-Chris Hohmann

[jason]

Ok - what about converting my database to Access 2002 - will I uncover
problems with data types etc ....another post further up suggested I should
not have any problems?

I would like to investigate this VBA startup you mentioned.....is there some
starter code that has been posted elsewhere....as I am clueless on DDL
objects inside Access....

Thanks for your help.

Jason
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:OO**************@TK2MSFTNGP12.phx.gbl...

"jason" <ja***@catamaranco.com> wrote in message
news:eK**************@TK2MSFTNGP11.phx.gbl...

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
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...

"jason" <ja***@catamaranco.com> wrote in message
news:eQ**************@TK2MSFTNGP09.phx.gbl...
> 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

Sorry, I don't believe there's any easy way around this limitation. Here
are some things you could try:
1. Someone in the m.p.access.* groups recommended creating a VBA (Visual
Basic for Applications) to check for DDL created objects at startup and
created visible clone thereof.
2. Muck around with the MSys tables. I suspect that the "hidden" nature
of the DDL created objects are tied to the Flags field of the
MSysObjects table. Unfortunately, their is no official documentation for
the MSys tables, so this is complete conjecture on my part.

WARNING!!!! DO NOT muck around with the MSys tables in a production
database. You are likely to render your database completely useless
during the course of testing. I can not recommended strongly enough that
you avoid editing the MSys tables unless it is absolutely necessary.

HTH
-Chris Hohmann

[Bob Barrows]

jason wrote:

Ok - what about converting my database to Access 2002 - will I uncover
problems with data types etc ....another post further up suggested I
should not have any problems?
Right.

I would like to investigate this VBA startup you mentioned.....is
there some starter code that has been posted elsewhere....as I am
clueless on DDL objects inside Access....

Probably in an Access newsgroup (try Google). You won't find any VBA code
here.

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"

[Chris Hohmann]

"jason" <ja***@catamaranco.com> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:OO**************@TK2MSFTNGP12.phx.gbl...

"jason" <ja***@catamaranco.com> wrote in message
news:eK**************@TK2MSFTNGP11.phx.gbl...

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
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Og**************@TK2MSFTNGP12.phx.gbl...
> "jason" <ja***@catamaranco.com> wrote in message
> news:eQ**************@TK2MSFTNGP09.phx.gbl...
> > 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

Sorry, I don't believe there's any easy way around this limitation. Here are some things you could try:
1. Someone in the m.p.access.* groups recommended creating a VBA (Visual Basic for Applications) to check for DDL created objects at startup and created visible clone thereof.
2. Muck around with the MSys tables. I suspect that the "hidden" nature of the DDL created objects are tied to the Flags field of the
MSysObjects table. Unfortunately, their is no official documentation for the MSys tables, so this is complete conjecture on my part.

WARNING!!!! DO NOT muck around with the MSys tables in a production
database. You are likely to render your database completely useless
during the course of testing. I can not recommended strongly enough that you avoid editing the MSys tables unless it is absolutely necessary.

HTH
-Chris Hohmann

Ok - what about converting my database to Access 2002 - will I uncover
problems with data types etc ....another post further up suggested I

should not have any problems?

I would like to investigate this VBA startup you mentioned.....is there some starter code that has been posted elsewhere....as I am clueless on DDL
objects inside Access....

Thanks for your help.

Jason

Converting your DB to Access2002 is probably your best bet. As stated in
responses to your other posts, both Access2K and Access2002 use the same
database engine (Jet 4.0). As such, conversion to Access2002 should be
relatively painless. This will be especially true for a database whose
primary role is a datastore for a web based application. It's unlikely
you have any forms/reports/modules which is were conversion conflict
usually manifest themselves. As always, due diligence is a must. Convert
a test copy of your database and make sure everything works as expected
before attempting the conversion on the production system.

Here's the google thread that mentions using a VBA startup script to
copy the hidden object.
http://groups.google.com/groups?thre....oke.nextra.no

HTH-
Chris Hohmann

[jason]

Thanks Bob - I'll investigate the .access forums!

- Jason
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:#s**************@tk2msftngp13.phx.gbl...

jason wrote:

Ok - what about converting my database to Access 2002 - will I uncover
problems with data types etc ....another post further up suggested I
should not have any problems?

Right.

I would like to investigate this VBA startup you mentioned.....is
there some starter code that has been posted elsewhere....as I am
clueless on DDL objects inside Access....

Probably in an Access newsgroup (try Google). You won't find any VBA code
here.

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"