Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 24th, 2005, 12:12 PM
Registered User
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default help with two linked tables query

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")
        & "A1.ClassCode, A1.ClassCode, A1.Date, A1.Location, A1.InstructorCode " _
        & "FROM (tblClassEmpl1 A1 LEFT JOIN dbo_AFFILIATE A2 ON A1.AffiliateID = A2.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"
    End If

    stDocName = "rpt-tblClassEMPL1"
    DoCmd.OpenReport stDocName, acViewNormal
    Set conn1 = Nothing
    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.

Old August 25th, 2005, 10:24 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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.

Old August 25th, 2005, 10:25 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked Tables edojan Access 1 July 20th, 2006 10:49 AM
Linked Tables ru1 Access 1 September 21st, 2005 07:24 AM
How to add linked tables objects to this query ? method Access 1 July 30th, 2005 01:54 AM
Linked Tables ricmar Access VBA 6 July 28th, 2004 05:08 PM
Access Linked Tables StephenDID Access ASP 6 October 6th, 2003 10:08 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.