Link to home
Start Free TrialLog in
Avatar of j-d
j-d

asked on

Access Project Run-Time error '7874': can't find the object table

Hello Experts:

I am using Access 2003 and VBA (SQL SERVER 2000 backend) to create a table . However when I try to access it to save its contents as a CSV file, it says

Run-Time error '7874': can't find the object table

HOWEVER, if I manually reconnect to the server (File --> Connection),  the table is there, and AFTER this reconnection, the code can find the table and do the export etc. properly.

To enact the above in code, I added code to close the connection, reconnect, and refresh the database window. It still gives me the above error. Please help if you can.

Here is the code:

' To create the table

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider = sqloledb.1; data source = myPC; intial catalog = myDB; integrated security = sspi"
cn.Execute "exec myDB.dbo.spmySP '" & strBatchDone & "' "

' Server = myPC; database = myDB; stored proc to create table = spmySP

' To close, reconnect and to refresh database window

cn.close
cn.Open "Provider = sqloledb.1; data source = myPC; intial catalog = myDB; integrated security = sspi"
RefreshDatabaseWindow

' To export a CSV file based on myTable, created using the stored proc above

   DoCmd.TransferText acExportDelim, , "myTable", "c:\temp\" strBatchDone & ".csv", -1

Please note that the syntax etc is correct as the above works fine when I go step by step manually. In code, the last line (DoCmd.Transfer......) gives the run-time '7874' eror, saying Myable can't be found, even though it is created and does exist.

TIA

j-d
Avatar of NArentz
NArentz

Hi,

I have had the same problem. It sounds like your connection to the SQL Server is not fast enought to keep up with your code. It may help for you to pause your code for a short time to wait for the project to check with SQL Server and then update the objects. The RefreshDatabaseWindow may be happening asynchronously so it doesn't finish running before your TransferText command runs. I hope this helps.

Cheers,

NArentz
Avatar of j-d

ASKER

Thanks - is there a simple code-based way to introduce delays (vba, vb.net)
ASKER CERTIFIED SOLUTION
Avatar of NArentz
NArentz

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of j-d

ASKER

Thank you so much for your assistance. The points are all yours :->

Also, I haven't tried these but I got the following as help from MSDN groups:

> > Maybe because the RefreshDatabaseWindow instruction is coming too fast:
the
> > SQL-Server didn't have the time to finalize the operation.  Try again by
> > introducing a small delay before the RefreshDatabaseWindow instruction
(or
> > maybe by creating a second table in the same SP ???)

Put this line into the Declarations section of a Module:
******************************
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Call it from code as (for a half-second delay):

Call Sleep(500)
*****************************

j-d

Hi j-d,

Glad I could help. It is not quite that SQL Server doesn't have the time to finalise the operation, it is that Access is not being forced to wait for that process to finish before proceeding. As far as Access is concerned it has issued the command and is now going on to the next command. Another way to solve this problem is to make the stored procedure return a value, say 1 or true if it completed successfully or 0 or false if it did not. That way Access has to wait for the result of the command before proceeding and you know the success or failure of the stored procedure. You can assign the return value to a variable and keep testing the variable until it holds the return value before proceeding to the next command in your code.

I had forgotten about that api call, thanks for reminding me!

Cheers,

NArentz