We help IT Professionals succeed at work.
Get Started

Problems with Excel connections to SQL Server

470 Views
Last Modified: 2014-09-17
Hi

I have an Excel spreadsheet that successfully uses data from an Access database over connections defined over the interactive Data / Connections definitions, and also using VBA code and ADODB connections used to access directly various tables using straightforward SQL statements.

Because all the tables in that Access DB should be migrated to SQL Server, I started to duplicate and modify the code I had for connecting to Access in order to connect to SQL Server tables. And that doesn't work.

Strange enough, The code to just establish the connection works. It looks like that:

    ' Instantiate the ADO-objects.
    Set connSQLS = New ADODB.Connection
    Set rsSQLS = New ADODB.Recordset
    Debug.Print "Access connection var and recordset var defined"
    
    ' Path to the database.
    sDBServer = "MyServer"
    sDBName = "MyDBTest"
    
    ' Create the connection string
    sConnectionStringSQLS = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=" & sDBServer & ";" & _
                              "InitialCatalog=" & sDBName & ";"
    'Open the connection
    connSQLS.Open (sConnectionStringSQLS)

Open in new window

This succeeds. I have an ON Error GoTo above it, and that is not triggered. The connection string is built from defining a connection over the Menus Data / Connections and copy/pasting the useful bits.

However, when I want to access one table to check if everything works, the following code fails:

    ' Check connection
    rsSQLS.Open "SELECT Max(ID) FROM MyTable", connSQLS

Open in new window

The error is: Invalid object name 'MyTable'

For the "MyTable" string, I've tried various syntaxes:
- <table name> appearing after "dbo." when looking at the DB tables in SQL Server Manager (which is what I use for Access tables when using the same code, just with the connection string for the Access DB)
- dbo_<table name>
- dbo.<table name>
None of it works.

What am I doing wrong ?

Thanks for help.
Comment
Watch Question
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 4 Answers and 16 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE