By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,127 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,127 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
24 Replies


P: n/a
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

Jul 19 '05 #2

P: n/a
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


Jul 19 '05 #3

P: n/a
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


Jul 19 '05 #4

P: n/a
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



Jul 19 '05 #5

P: n/a
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

Jul 19 '05 #6

P: n/a
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

Jul 19 '05 #7

P: n/a
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


Jul 19 '05 #8

P: n/a
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


Jul 19 '05 #9

P: n/a

"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
Jul 19 '05 #10

P: n/a
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.
Jul 19 '05 #11

P: n/a
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])

Jul 19 '05 #12

P: n/a
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])


Jul 19 '05 #13

P: n/a
"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
Jul 19 '05 #14

P: n/a
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.

Jul 19 '05 #15

P: n/a
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

Jul 19 '05 #16

P: n/a
"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
Jul 19 '05 #17

P: n/a
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"
Jul 19 '05 #18

P: n/a
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

Jul 19 '05 #19

P: n/a
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"

Jul 19 '05 #20

P: n/a

"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
Jul 19 '05 #21

P: n/a
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

Jul 19 '05 #22

P: n/a
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"
Jul 19 '05 #23

P: n/a
"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
Jul 19 '05 #24

P: n/a
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"

Jul 19 '05 #25

This discussion thread is closed

Replies have been disabled for this discussion.