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
j-dAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NArentzConnect With a Mentor Commented:
Hi,

You can either use DoEvents (VBA) which yields execution so that the operating system can process other events (I have never had a problem using DoEvents but it can have unexpected results/time delays) or you can use a timer routine. Here's a sample timer routine:

Function Wait(pintDelay As Integer, pintDispHrglass As Integer)

  Dim dblDelayEnd As Double

  DoCmd.Hourglass DispHrglass

  dblDelayEnd = DateAdd("s", pintDelay, Now)
  While DateDiff("s", Now, dblDelayEnd) > 0
  Wend

  DoCmd.Hourglass False

End Function

If you wanted to incorporate this into your code rather than call it as a separate function, you could just use:

Dim dblDelayEnd as Double
Dim intDelay as Integer
intDelay = 1 ' or whatever interval you decide on
dblDelayEnd = DateAdd("s", intDelay, Now)
While DateDiff("s",Now, dblEdlayEnd) > 0
Wend

Cheers,

NArentz
0
 
NArentzCommented:
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
0
 
j-dAuthor Commented:
Thanks - is there a simple code-based way to introduce delays (vba, vb.net)
0
 
j-dAuthor Commented:
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

0
 
NArentzCommented:
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
0
All Courses

From novice to tech pro — start learning today.