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

[Guest]

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

 

[Sylvain Lafontaine]

Might be an error about ownership (dbo. and the like). Is the ownership of
the table the same as the user specified in the connection string after your
sp has been executed? If you take a look at the database window, right
after your refresh command, did the new table appears in the list?

Second, I'm not sure about your description, but is this sp executed only
once in the lifetime of your database or if you are running it each time,
just before trying to save its content?

Also, when Access connect to a project, it opens 3 connections to the
database and, with your code, you open a fourth. I don't know if closing
this fourth one will do anything.

Maybe in the TransferText command, you should try to specify the owner of
the table, something like "dbo.MyTable" instead of "MyTable". (Don't forget
to replace dbo with the proper name of the owner.)

S. L.

 

[Guest]

Thanks Sylvain. Yes, the ownership is the same and I did try putting full
names in the transfertext command, but to no avail. The new table does not
appear in the list after the RefreshDatabaseWindow command, but only after I
go to File -->Connection, and then select OK, so basically after manually
reconnecting to the server. Trying to close and then reconnecting to the
server via code (as shown) doesn't do anything.

Regards

J

 

[Sylvain Lafontaine]

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

S. L.

 

[Guest]

Is there a simple code-based way to introduce this delay? Thanks

 

[Ron Hinds]

Is there a simple code-based way to introduce this delay? Thanks

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)

 

[Guest]

Much thanks to Sylvain and Ron for the help. I believe the problem is solved
now and your suggestions were very helpful.

 

[markus peter]

Hello Sylvain and Jay

Did this really solve it? I have encountered the same problem. The
RefreshDatabaseWindow works without having to use a delay, but only (!) if
the database window is open and shows the tables (not forms or reports etc).

Is there really no other way to force access to update its list of tables on
the server?

markus