I have the following snippet of code that uses two linked tables, 1 to another Access db, and the other to a Sybase db. If I run the local query the user has to enter the login+password to the Sybase table. So I wrote some vba code that I thought would run the same query but make the connection for the user. Here is the code:

    Dim connDIR As String
    Dim conn1 As ADODB.Connection
    Dim rst As Recordset
    Dim qry As String
    Set conn1 = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.RecordSet")
    qry = "SELECT A3.DEPARTMENT, A2.DISPLAY_NAME, " _
        & "A1.ClassCode, A1.ClassCode, A1.Date, A1.Location, A1.InstructorCode " _
        & "FROM (tblClassEmpl1 A1 LEFT JOIN dbo_AFFILIATE A2 ON A1.AffiliateID = A2.AFFILIATE_ID) " _
        & "LEFT JOIN dbo_EMPLOYEE_INFO A3 ON A2.AFFILIATE_ID = A3.AFFILIATE_ID"

    connDIR = "DATA SOURCE=SybaseDB;USER ID='myID';PASSWORD='myPass'"
    conn1.Open connDIR

    rst.Open qry, conn1
    If Not (rst.EOF) And Not (rst.BOF = True) Then
      Do While Not rst.EOF
      'put into a table to run the report off
       DoCmd.OutputTo acOutputTable, "T1"
        rst.MoveNext
      Loop
    End If

    stDocName = "rpt-tblClassEMPL1"
    DoCmd.OpenReport stDocName, acViewNormal
    Set conn1 = Nothing
    rst.Close
    Set rst = Nothing
    MsgBox "Classes Report printed at your default printer."

I get the following error on the qry:
"[ODBC Sysbase driver][SQL Server]tblClassEmpl1 not found."
which is a the Access table that is linked.

Any help or ideas would be greatly appreciated.

Mrs.Footohi

Well, the first problem is that you are using conn1 to connect to the Sybase DBMS, and including tblClassEmpl1 in the query string. The server is trying to build the recordset on the server, not on your local machine, so unless the Sybase server has a connection to the Access table, this is not going to work.

It is trying to compile the recordset on the server, then send the results back to your Access request.

What you need to do is make a query to the entire Sybase table with no joins or parameters, then once the data is pulled as a recordset, run a local query using the Access linked table.

What I would suggest is to grab the data from the local Access table that you are using as parameters in the Sybase query, and then just pass the parameters to a view you build on the server. If it is an AffiliateID, for example, pass the AffiliateID to the Sybase view, and process the query on the server, then transfer just those results to a temporary table and base your report on that table. You will need to delete everything in that table before you run each report.

If this is ust an OLAP front end, what I do is to download all of my SQL Server tables as the application opens passing username and password, and then process all the queries etc locally, so there is no additional network traffic after the first pull. If you are doing any OLTP, you don't really want to do this. It depends on the number of concurrent users.

mmcdonal

Or make a link to the Access database from the Sybase server.

mmcdonal