Unable to connect to SQL 2000 from VBA Excel

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

Windows 10VBASQL

Last Comment

Mark Bakelaar

8/22/2022 - Mon

ste5an

3/3/2020

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

3/4/2020

ASKER

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

ste5an

3/5/2020

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Mark Bakelaar

3/5/2020

ASKER

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

ASKER CERTIFIED SOLUTION

ste5an

3/5/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Mark Bakelaar

3/5/2020

ASKER

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