Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: ODBC to SQL Server

    I am trying to append data from an SQL Server table into an Access table. I have successfully setup a DSN, and when I use this connection string in VBA, I can successfully query data in the SQL Server:

    Code:
    cnn1.Open "Driver={SQL Server}; Server=NW-ITD-SQLBACK;" & _
              "DSN=[Epicor Test DB]; UID=xxxx; Pwd=xxxx;"
    However, when I insert that connection string into the Source Connect Str field in the Query Properties in query view in Access, I get the following error:

    Could not find installable ISAM.

    I tried altering the Source Connect Str field to this (as recommended by Access Help):

    Code:
    ODBC;DSN=[Epicor Test DB];UID=xxxx;PWD=xxxx;DATABASE=enatworld
    However, this produces the following errorr:

    ODBC--connection to '[Epicor Test DB]' failed.

    Anyone have any help/ideas?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    If you're going to use ADO for your connection to read from SQL Server then your connection string should JUST contain the DSN name ...

    DSN=[WhatEver];

    Have your DSN do the UID and PWD ...

    You do not want to use this with a stored query in Access. Just do some VBA code to read and write the data ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    It turns out it wouldn't work because of the square brackets in the string. I removed them and it works fine. I've also taken your advice however and have removed the UID and PWD. Cheers.

Posting Permissions

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