Solved

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

Posted on 2004-10-25
450 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
Question by:j-d
    5 Comments
     
    LVL 2

    Expert Comment

    by: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
    0
     

    Author Comment

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

    Accepted Solution

    by:
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now