Results 1 to 4 of 4

Thread: Need Help!!

  1. #1
    Join Date
    Jan 2005
    Location
    Toronto
    Posts
    5

    Unanswered: Need Help!!

    I have established a system DSN Data source named 'work' connect to local SQL Server 2000 and the connection test was sucessful.

    I also have written the VB 6.0 code below to connect local Microsoft Access:
    dim dbLocal as ADODB.Connection
    set dbLocal = New ADODB.Connection dbLocal.Open "Provider='Microsoft.Jet.OLEDB.4.0';data source='C:\localdb\data\localdb.mdb'"

    I have some store procedures. some of them accessed the local Microsoft Access tables, some of them accessed both local Microsoft Access tables and local SQL Server tables.

    When I tried to execute the store procedures only accessed tthe local Access tables, they passed.
    When I tried to execute the store procedures that linked to SQL Server tables, the error is:
    ODBC--connection to work failed.

    What should I do?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It looks to me like your connection string points to the Access database, and
    not your SQL Server datasource.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2005
    Location
    Toronto
    Posts
    5
    hi, my connection to SQL Server is OK.
    i just cannot connect to linked table in SQL Server through MS Access storage procedure.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Help me clarify what you are trying to do:

    Are you trying to call a Stored Procedure in SQL Server from Access
    -- or --
    Execute some VBA code to update tables in SQL Server?

    Either way, what does the connection string for your SQL server database look like?
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •