Problems with Excel connections to SQL Server

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.
LVL 1
bthouinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
What is the actual table name? (I assume not MyTable)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Substitute you SELECT with this one:
SELECT DB_NAME()

An tell us what it returns.
0
mcsweenSr. Network AdministratorCommented:
Try this connection string instead:
connSQLS.Open("Driver=SQL Server;Server=SERVER_NAME;Database=DATABASE_NAME")

Open in new window


Also, make sure whatever Query you are running works and returns results in SQL Management Studio.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Vitor MontalvãoMSSQL Senior EngineerCommented:
And also, for SQL Server you shouldn't use "Data Source" and "InitialCatalog" for the connection string.
Instead you should use "Server" and "Database" respectively.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Experts,

"Driver=SQL Server" opens an ODBC connection, and does not use ADODB.
With ADODB it does not matter whether you use "Server" or "Data Source" (same for db).

And please note that the connection works, the table cannot be accessed.


bthouin,

Are you certain that your OS user has the correct privileges to see tables? If in Management Studio, do you also log in with your OS user (integrated security), or do you provide a SQL user?
0
bthouinAuthor Commented:
Well, first suggestion (DB_NAME()), already explains the problem: it yields "master", which is of course the defalt DB and not the one I want. The 2 other posts point in the same direction: my connection string, although not yielding an error, is obviously completely unusable...

Ok, here is the score:
- Driver=SQL Server does not work. I still have to use Provider=SQLOLEDB.1
- Replacing Data Source and Initial Catalog is good, so Vitor has the right answers

So with the corresponding connection string:

sConnectionStringSQLS = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Server=" & sDBServer & ";" & "Database=" & sDBName & ";"

IT WORKS with a normal SELECT statement ! So that's a first success.

BUT: Now I try to insert a row in that table, and that FAILS again. My syntax is as follows:

    rsSQLS.Open "SELECT * FROM tbApplicationLogging", connSQLS
    rsSQLS.AddNew
        rsSQLS!ApplicationName = "TestApp"
        rsSQLS!LogText = "Test"
    rsSQLS.Update

(I tried rsSQLS.Open "tbApplicationLogging", connSQLS, as I would do with Access, but that gave me an error message)

The above doesn't give me any error, but it does not insert the row either. What's wrong ? (I don't want to use the clumsy syntax with "INSERT INTO <table name> (<fields list>) VALUES (<field values>) if possible).
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
We use both "Data Source ...; Initial Catalog" and "Server ...; Database", and they are interchangeable. However, it should be "Initial Catalog", not "InitialCatalog".
Sadly "wrong" options often are just ignored. That explains why your DB was not set correctly.

What's the error message if you use the table-only syntax? Probably that will give a hint why the insert does not work.
0
mcsweenSr. Network AdministratorCommented:
Use the connection object with the exec method to do an insert.

connSQLS.Exec "INSERT INTO somedb (Row1, Row2) VALUES ('Val1','Val2')

Open in new window

0
bthouinAuthor Commented:
>>What's the error message if you use the table-only syntax? <<
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Ah, this table has an Identity as PK. In Access VBA I normally use DAO, and I have to add dbSeeChanges in all OpenRecordset statements. Is there an equivalent when using ADODB ?
0
mcsweenSr. Network AdministratorCommented:
That should read
connSQLS.Exec "INSERT INTO somedb (Col1, Col2) VALUES ('Val1','Val2')"

Open in new window

0
bthouinAuthor Commented:
@mcsween: that syntax is the one I would like to avoid, especially for tables with lots of fields to set, it's a pain somewhere. AddNew and Update seem to be supported, the IDE shows them. so can I not use these and set each field separately ?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Probably you have to provide the type of cursor to use when opening the recordset, like with
rsSQLS.Open "tbApplicationLogging", connSQLS, adOpenDynamic

Open in new window

0
mcsweenSr. Network AdministratorCommented:
Try this.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
rsSQLS.CursorLocation = adUseClient
rsSQLS.Open "SELECT * FROM tbApplicationLogging", connSQLS, adOpenStatic, adLockOptimistic
rsSQL.AddNew
rsSQL("Field1") = "Value1"
rsSQL("Field2") = "Value2"
rsSQL.Update

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
bthouin, you don't need the 'SELECT * ....' stuff for add new record to a table. You just need to specify the table name, as this:

    rsSQLS.Open "tbApplicationLogging", connSQLS
     rsSQLS.AddNew
         rsSQLS!ApplicationName = "TestApp"
         rsSQLS!LogText = "Test"
     rsSQLS.Update

Open in new window

0
bthouinAuthor Commented:
@Olemo, mcsween

I uncommented the .CursorLocation statement which was correct, but as I was not sure, I had commented it out.
I used adOpenDynamic and otherwise the code from mcsween and it worked brilliantly !

So thanks to you all guys, now I have a working code which I can expand and enhance.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why you accepted solutions that wasn't answers for your original questions?

 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 ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.