• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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.
0
bthouin
Asked:
bthouin
  • 4
  • 4
  • 4
  • +2
4 Solutions
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
QlemoDeveloperCommented:
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
 
QlemoDeveloperCommented:
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
 
QlemoDeveloperCommented:
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now