Make table query works intermittently

[quartz]

I am using Office 2003 on Windows XP.

I am running the following code. This is running from a standard code module
in Excel, querying Oracle, and making a table into an Access DB. The problem
is, sometimes it runs fine, other times I get "ODBC Call Fails" error:

Dim sSQL as String
Dim sConnect as String
Dim sFullNameDB as String

sConnect = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=MyServer;DBQ=MyDBQ;UID=***;PWD=***;"

sFullNameDB = "C:\Temp\MyAccessDB.mdb"

sSQL = "SELECT "
sSQL = sSQL & "[GL].ACCTNO, "
sSQL = sSQL & "[GL].ACCOUNT_DESC, "
....<OTHER SQL - REMOVED FOR THIS EXAMPLE>...
sSQL = sSQL & "INTO "
sSQL = sSQL & "[IMPORT] "
sSQL = sSQL & "FROM "
sSQL = sSQL & "[ODBC;" & sConnect & "].[APPS.GLBALVW] AS [GL] "
sSQL = sSQL & "WHERE "
sSQL = sSQL & "[GL].PERIOD_NAME IN ('Sep-03', 'Sep-04') AND "
sSQL = sSQL & "[GL].FUND BETWEEN '800' AND '999' AND "
sSQL = sSQL & "[GL].ACCOUNT BETWEEN '0000' AND '8999';"

Set cnADO = New ADODB.Connection
cnADO.CommandTimeout = 0
cnADO.CursorLocation = adUseClient
cnADO.Provider = "Microsoft.Jet.OLEDB.4.0"
cnADO.ConnectionString = sFullNameDB
cnADO.Open
cnADO.Execute sSQL
cnADO.Close
Set cnADO = Nothing

Please note I have timeout set to zero.
Any help appreciated. Thanks in advance.

 

[K Dales]

A couple of questions:
1) Are you sure no one else is in Access when it bombs - especially if they
have the db open in exclusive mode because they are doing design changes?
2) Does it usually run OK the first time (in a session) and then bomb on the
second call to your sub? If so, you may be blocking yourself! When you make
a table, Access will give you exclusive access to the db so your design
changes do not conflict with other users. Then, when you close the
connection, it has to release those locks - if you try hitting Access again
too quickly it may not have finished this and might still see the db as being
in exclusive use (it has to physically access a file on your hard drive, so
it takes a bit of time - enough that your code could get ahead of it). You
might consider making cnADO a Global variable. I have done this when I know
I need to run multiple queries from code: I have an "initiating" sub that
opens the connection, then I run the sub(s) that perform the queries, then
when done I run a "cleanup" sub that closes the connection. This is a more
efficient way of making multiple hits to the db - just be sure to have error
handlers to close that connection if anything goes wrong!

 

[quartz]

K, thanks for your reply:

1) Absolutely, DB is in development and I am the only one with access to it.

2) I thought of this too. I have even rebooted my computer thinking that
maybe the connections and what-not are crossing wires in memory, or locking
issues inside the DB itself as you mentioned. But even after rebooting, it
may not run for 2-3 tries, then is runs for 6 tries in a row, then it may
fail the rest of the day! Without making ANY changes in the code at all.

3) I do like your suggestion of making cnADO global - and I will adapt my
code, but I'm so frustrated with it right now, that I plan to abandon the
"make table" altogether unless someone can fix it for me...I've tried
everything I can think of.

It's too bad too, because the make table query is SOOO convenient and great,
when it works :-(

Do you have any other suggestions?

How about if I send the data into a Text file first, then upload to Access?
Can you edit my example code to create a text file?

Thanks a lot for your time and assistance.

A couple of questions:
1) Are you sure no one else is in Access when it bombs - especially if they
have the db open in exclusive mode because they are doing design changes?
2) Does it usually run OK the first time (in a session) and then bomb on the
second call to your sub? If so, you may be blocking yourself! When you make
a table, Access will give you exclusive access to the db so your design
changes do not conflict with other users. Then, when you close the
connection, it has to release those locks - if you try hitting Access again
too quickly it may not have finished this and might still see the db as being
in exclusive use (it has to physically access a file on your hard drive, so
it takes a bit of time - enough that your code could get ahead of it). You
might consider making cnADO a Global variable. I have done this when I know
I need to run multiple queries from code: I have an "initiating" sub that
opens the connection, then I run the sub(s) that perform the queries, then
when done I run a "cleanup" sub that closes the connection. This is a more
efficient way of making multiple hits to the db - just be sure to have error
handlers to close that connection if anything goes wrong!

--
- K Dales

I am using Office 2003 on Windows XP.

I am running the following code. This is running from a standard code module
in Excel, querying Oracle, and making a table into an Access DB. The problem
is, sometimes it runs fine, other times I get "ODBC Call Fails" error:

Dim sSQL as String
Dim sConnect as String
Dim sFullNameDB as String

sConnect = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=MyServer;DBQ=MyDBQ;UID=***;PWD=***;"

sFullNameDB = "C:\Temp\MyAccessDB.mdb"

sSQL = "SELECT "
sSQL = sSQL & "[GL].ACCTNO, "
sSQL = sSQL & "[GL].ACCOUNT_DESC, "
...<OTHER SQL - REMOVED FOR THIS EXAMPLE>...
sSQL = sSQL & "INTO "
sSQL = sSQL & "[IMPORT] "
sSQL = sSQL & "FROM "
sSQL = sSQL & "[ODBC;" & sConnect & "].[APPS.GLBALVW] AS [GL] "
sSQL = sSQL & "WHERE "
sSQL = sSQL & "[GL].PERIOD_NAME IN ('Sep-03', 'Sep-04') AND "
sSQL = sSQL & "[GL].FUND BETWEEN '800' AND '999' AND "
sSQL = sSQL & "[GL].ACCOUNT BETWEEN '0000' AND '8999';"

Set cnADO = New ADODB.Connection
cnADO.CommandTimeout = 0
cnADO.CursorLocation = adUseClient
cnADO.Provider = "Microsoft.Jet.OLEDB.4.0"
cnADO.ConnectionString = sFullNameDB
cnADO.Open
cnADO.Execute sSQL
cnADO.Close
Set cnADO = Nothing

Please note I have timeout set to zero.
Any help appreciated. Thanks in advance.