Connection.Execute Problems

[Miquella]

I'm using A03 at home and A97 at work... I created a function that stores the ascii values of an entire file into a table, so that I can export the file at any time. The function works great at home, but when I brought it to work, it doesn't work anymore.
I was using the:

CurrentProject.Connection.Execute "...

I found out that the CurrentProject object doesn't work, I guess, in A97, so I tried:

CurrentDb.Connection.Execute "...

but all this does is give me is 'Operation is not supported for this type of object.'

Does anyone know how I might fix this?

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[PHV]

And what about this ?
DoCmd.RunSQL "...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

CurrentProject is a member of the Access application object, starting with the 2000 version (and represents an ADO connection to the current database)

CurrentDB is also a member of the Access application object, and refers to the current (DAO) database.

The syntax is

[tt]currentdb.execute <your sql string or queryname>[/tt]

Roy-Vidar

 

[Miquella]

And on the same subject... I use one of my Connection.Execute functions to delete the table, then re-create it, so that I know the structure is correct.

Does anyone know if I can use the 'DROP TABLE IF EXISTS' SQL statement? I can't get the 'IF EXISTS' part to work, pretty sure I'm using the correct syntax, but maybe not?
So, I'm just trapping the error for now, but I'd like to not have to do that, in case of other errors.

Regards,

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[Miquella]

Perfect, thank you Roy!

BTW: I didn't use DoCmd.RunSQL because it caused Access to prompt for the table deletion, that's something that I don't want the user to have to deal with.

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[Tranman]

FBean,
Oracle guy, huh? The "If Exists" stuff does not work in Access, but what you can do is iterate through the tables collection, (what the heck, blow a couple of milliseconds , and check each one for the name you're looking for and if it's a match, drop it.

I think I've also just done the drop, and captured the error and resumed next (seems like it's a unique error value), if the code threw an error because the table did not exist.

My recollection is that the currentproject.Connection.execute "Drop table <whatever>", works in '97 (yes, you can use the ADO in '97, it just didn't exist at the time '97 came out.), if you add a reference to the ADO. Not sure about that, but I think so... That would let you run the same code both places.

Tranman

 

[JerryKlmns]

FBean

I have the same problem so I use this trick like Tranman

On error resume next
CurrentProject.Connection.Execute "Drop Table myTableName"
On error goto 0

If the table does exist then it is deleted, else the error produced is ignored and go on executing the code. If you want to turn on error handling, modify the last On Error statement.

 

[Miquella]

Yes, that's the same type of code that I'm using, but with RoyVidar's direction on the Execute command.
I have to be able to use it on a clean installation, across multiple OS's and I just have to have it be able to work no matter what. So I want to keep in fairly simplistic, but again, thank you for your responses!

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[PHV]

I didn't use DoCmd.RunSQL because it caused Access to prompt for the table deletion
Take a look at the DoCmd.SetWarnings method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244