We help IT Professionals succeed at work.

Unable to connect to SQL 2000 from VBA Excel

Mark Bakelaar
Mark Bakelaar asked
on
Hi Experts,

I have an ODBC connection setup in Windows 10 to a SQL 2000 DB. Using this connection I can connect from Excel and retrieve data from any table in the DB. This is also working fine.

I want to do the same thing from VBA Excel. Below the code (names of DB and Initial Catalog are changed). I can connect to the DB, but I get an error on the SQL query. The message is "Invalid object name 'SALESTABLE'". I get the same message if I change to another table.

What can be the issue?

Regards,
Mark

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConnString As String
 
    ' Create the connection string
    strConnString = "Provider=SQLOLEDB.1;Data source=MyDatasource;" & _
                    "Initial Catalog=MyCatalog;" & _
                    "INTEGRATED SECURITY=SSPI;"
                   
    ' Create the Connection and Recordset objects
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    ' Open the connection and execute
    conn.Open strConnString
    Set rs = conn.Execute("SELECT * FROM SALESTABLE")
   
    ' Check if there is data
    If Not rs.EOF Then
        Sheets(3).Range("A1").CopyFromRecordset rs
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
   
End Sub
Comment
Watch Question

View Solution Only

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
First of all: SQL Server 2000 is out of support since over 6 years...

On part of the problem is that you're mixing the ways you open an recordset and the other is propably an object name error. Test how the table is named:

Option Explicit

Public Sub Test()

  Dim Connection As ADODB.Connection
  Dim Recordset As ADODB.Recordset

  Set Connection = CreateConnection
  Set Recordset = CreateRecordset(Connection, "[dbo].[SalesTable]")
  CopyData Recordset
  Set Recordset = Nothing
  Set Connection = Nothing

End Sub

Public Sub Test2()

  Const SQL As String = _
    "SELECT QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + TABLE_NAME " & _
    "FROM INFORMATION_SCHEMA.TABLES " & _
    "WHERE TABLE_NAME = 'Neu';"

  Dim Connection As ADODB.Connection
  Dim Recordset As ADODB.Recordset

  Set Connection = CreateConnection
  Set Recordset = CreateRecordset2(Connection, SQL)
  If Not Recordset.BOF And Not Recordset.EOF Then
    Debug.Print "Found table: " & Recordset.Fields(0).Value
  Else
    Debug.Print "No table found."
  End If

  Set Recordset = Nothing
  Set Connection = Nothing

End Sub

Public Function CreateConnection() As ADODB.Connection

  On Local Error GoTo LocalError

  ' Use your connection string here.
  Const CONNECTION_STRING As String = "Provider=SQLOLEDB.1;Data source=(local);Initial Catalog=master;INTEGRATED SECURITY=SSPI;"

  Set CreateConnection = New ADODB.Connection
  CreateConnection.Open CONNECTION_STRING
  Exit Function

LocalError:
  Debug.Print "CreateConnection(): ERROR " & Err.Number & " - " & Err.Description

End Function

Public Sub CopyData(ByRef CRecordset As ADODB.Recordset)

  If Not CRecordset.BOF And Not CRecordset.EOF Then
    Sheets(3).Range("A1").CopyFromRecordset CRecordset
  Else
    MsgBox "Error: No records returned.", vbCritical
  End If

End Sub

Public Function CreateRecordset(ByVal CConnection As ADODB.Connection, ByVal CTableName As String) As ADODB.Recordset

  On Local Error GoTo LocalError

  Set CreateRecordset = New ADODB.Recordset
  CreateRecordset.Open CTableName, CConnection, adOpenDynamic, adLockReadOnly, adCmdTable
  Exit Function

LocalError:
  Debug.Print "CreateRecordset(): ERROR " & Err.Number & " - " & Err.Description

End Function

Public Function CreateRecordset2(ByVal CConnection As ADODB.Connection, ByVal CSqlStatement As String) As ADODB.Recordset

  On Local Error GoTo LocalError

  Set CreateRecordset2 = New ADODB.Recordset
  CreateRecordset2.Open CSqlStatement, CConnection, adOpenDynamic, adLockReadOnly
  Exit Function

LocalError:
  Debug.Print "CreateRecordset2(): ERROR " & Err.Number & " - " & Err.Description

End Function

Open in new window

Mark Bakelaar

Author

Commented:
Thanks for the quick reply. I tried the code you provided. The error is still similar:

CreateRecordset(): ERROR -2147217865 - Invalid object name 'dbo.SalesTable'.

For some reason the SalesTable is not found from VBA Excel. Using the Excel Data import I have not issues finding this and other tables.

I tried looking for alternative ways to find out why the tables are not found from VBA Excel, but no luck so far.

Hope you have some suggestions.

Regards,
Mark
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Is this the correct name as reported by the Test2? Modify it to see how your table is really named:

Public Sub FindTable(ByVal CPartialTableName As String)

  Const SQL As String = _
    "SELECT QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + TABLE_NAME " & _
    "FROM INFORMATION_SCHEMA.TABLES " & _
    "WHERE TABLE_NAME LIKE '%?%';"

  Dim Connection As ADODB.Connection
  Dim Recordset As ADODB.Recordset

  Set Connection = CreateConnection
  Set Recordset = CreateRecordset2(Connection, Replace(SQL, "?", CPartialTableName))
  If Not Recordset.BOF And Not Recordset.EOF Then
    Debug.Print "Found table(s).."
    Debug.Print "--"
    Do While Not Recordset.EOF
      Debug.Print vbTab & Recordset.Fields(0).Value
      Recordset.MoveNext
    Loop

    Debug.Print "--"
  Else
    Debug.Print "No table found."
  End If

  Set Recordset = Nothing
  Set Connection = Nothing

End Sub

Open in new window

Then run FindTable "Sales" in the Immediate Window.. the result then may look like

FindTable "e"
Found table(s)..
--
    [master].[dbo].spt_fallback_dev
    [master].[dbo].Neu
    [master].[dbo].spt_values
    [master].[dbo].MSreplication_options
--

Open in new window

Mark Bakelaar

Author

Commented:
Thanks again!

The search is in the correct DB, but not the correct table schema.

[AX30PROD].[bmssa].SYSDATASEARCH
[AX30PROD].[bmssa].SYSDATASEARCHDAEMONTABLE
[AX30PROD].[bmssa].SYSDOCCAPTUREFORM
[AX30PROD].[bmssa].SYSDOCCAPTUREFORMSTEP
[AX30PROD].[bmssa].SYSEMAILMESSAGESYSTEMTABLE
[AX30PROD].[bmssa].SYSEMAILMESSAGETABLE
[AX30PROD].[bmssa].SYSEMAILPARAMETERS
[AX30PROD].[bmssa].SYSEMAILSYSTEMTABLE
...

Is there a possibility to find out where to look?

Mark
Senior Developer
CERTIFIED EXPERT
Commented:
Okay, you hopefully changed the search term to "sales" before running it?
I used "e" cause it don't have a table named sales to test it with that search term.

Otherwise:

Then there is no sales table or you don't have the permission to view that schema, cause INFORMATION_SCHEMA.TABLES gives you the information about all schemas you're allowed to view.

So the problem is imho either

A) wrong database server or instance
B) different authentication/credentials leading to different permissions
Mark Bakelaar

Author

Commented:
Thanks again.

Problem solved. I needed to change the sql query to the following

Set rs = conn.Execute("SELECT * FROM [AX30PROD].[BMSSA].SALESTABLE;")

Now the table is found correctly.

Regards,
Mark