Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-25
5
Medium Priority
?
489 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:j-d
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:NArentz
ID: 12418897
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
 

Author Comment

by:j-d
ID: 12422317
Thanks - is there a simple code-based way to introduce delays (vba, vb.net)
0
 
LVL 2

Accepted Solution

by:
NArentz earned 1000 total points
ID: 12427837
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
 

Author Comment

by:j-d
ID: 12435480
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
 
LVL 2

Expert Comment

by:NArentz
ID: 12439075
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question