Can't get ADODB Connection RRS feed

  • Question

  • Hi folks.

    Been awhile since I've had to do an ADO connection to SQL Server, so I'm a little rusty. I'm using the following code to establish the connection...

    I'm getting the following error...

    What's got me stumped is that I have another Access database for this same client, accessing the same server but a different database, and using the same connection string with 2 differences: different DATABASE, and using SQL Server login credentials. I have db_owner role.

    Any help would be appreciated.

    Darrell H Burns

    Tuesday, May 7, 2019 8:37 PM

All replies

  • When an ODBC error occurs, there are often multiple error messages available in an array. Do this in the Immediate window:

    If it gives you more than 1, do this:

    Then hopefully the problem will reveal itself.

    -Tom. Microsoft Access MVP

    Wednesday, May 8, 2019 2:08 AM
  • Tried that. The error count was 1...

    No current record.

    Can't say that helps me any.

    Darrell H Burns

    Wednesday, May 8, 2019 3:22 PM
  • I would further debug this outside of Access, for example using SSMS to connect to the server with same credentials, and/or by creating an empty UDL file on the desktop, opening it, and configuring it.

    -Tom. Microsoft Access MVP

    Wednesday, May 8, 2019 3:32 PM
  • Yes, I can connect to the database through SSMS using the same Windows auth.

    What I'm wondering is if I can connect just fine to a different database on the server, what could be different about this database?

    Darrell H Burns

    Wednesday, May 8, 2019 3:43 PM
  • Have you setup the ODBC data sources to SQL server ?

    External>ODBC database >Machine and data source 

    Wednesday, May 8, 2019 5:16 PM
  • To answer that correctly I would need access to the server.

    But one thing is true: if a user has access to database A on some server, that means NOTHING about their access to database B on the same server.

    Yet you say the connection via SSMS works. That's a good step.
    Next is: would it still work if connected using SQLNCILI11? My previous UDL suggestion will tell you.

    -Tom. Microsoft Access MVP

    Thursday, May 9, 2019 1:59 AM
  • Do you linked tables to that server work?

    And, I note you are using the native 11 drivers.

    They are not installed by default on each workstation. They are better drivers then the "legacy" SQL driver, but are NOT installed on each computer.

    So, in some cases, for ease of deployment, I still use the "built in" windows ODBC sql driver - they are installed on all versions of windows by default.

    So, I would check if native 11 drivers been installed. The downside of these "better" and "newer" drivers is that you have to download and install the native drivers on each workstation.

    I suppose I could ask why introduce ADO into your application but that's really another topic. If you have linked tables, then you can always go:

    currentDb.Execute "my sql"

    And, for linked tables?
    dim rst as DAO.RecordSet

    set rst = currentdb.OpenRecordSet("my table or sql", currentdb.OpenRecordset("sdflj",dbOpenDynaset, dbSeeChanges).

    However, regardless of ADO or DAO, I would check for a missing native 11 on the offending workstation. Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Friday, May 10, 2019 7:06 AM