ms access connect to 2 sql server databases on the same server

Question

I am in the process of converting an Access application to use a SQL Server backend while still using the Access front end forms. Sounds like fun I know.

This application needs data access to 2 SQL Server databases that are on the same server. There are numerous inline sql query strings that attempt to connect to both databases at the same time on a single ADODB connection. This is failing because I am expecting records but none are returned.

What is the best way to fix this? Is there any way to use these sql strings or must it all be converted to stored procedures? Thanks for any help.

Here is some code:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

With conn
    .Provider = "sqlncli11" 
    .ConnectionString = "Server=[MY_SERVER];Database=[MY_DATABASE];User Id=sa; Password=password;"
    .Open
End With

Dim str As String
str = "SELECT TABLE_DB1.Parent_Item_No FROM TABLE_DB1 INNER JOIN [DB2].[dbo].TABLE_DB2 ON (TABLE_DB1.Comp_Item_No = " & _
            "TABLE_DB2.item_no) AND (TABLE_DB1.Loc = TABLE_DB2.loc) " & _
            "GROUP BY TABLE_DB1.Parent_Item_No " & _
            "HAVING (((TABLE_DB1.Parent_Item_No)='" & str_Assembly & "'));"

With rst
    .Open str, conn, adOpenKeyset, adLockOptimistic ' this fails to return records

    If .RecordCount > 0 Then 
        'Do Stuff
    Else
    'Do Other Stuff
    End If 

End With

Why is it failing? please post the error or explain the issue — Nick.Mc, Sep 01 '15 at 01:57
Looking at your SQL, this doesn't make much sense: `[MY_DATABASE] INNER JOIN [DATABASE_2]`. You need a database, schema and object there. Something like `[MY_DATABASE].dbo.TableName` — Nick.Mc, Sep 01 '15 at 01:57
Nick, you are correct I had the sql syntax wrong. I attempted to fix it above. — user3889026, Sep 02 '15 at 01:56
I don't get an error, just no records returned. I used the SQL Server Profiler to check that the query was coming in correctly and it was. I have since found out that I can use the docmd.runsql to execute these queries as long as they don't return records. But it appears I need to use stored procs to return records. — user3889026, Sep 02 '15 at 01:56
To clarify my comment above. The sql syntax was wrong above but correct in my application code. — user3889026, Sep 02 '15 at 02:03
Can you correct the code above? It still has errors, unless `[MY_DATABASE]` is a table. Copy the SQL out of Profiler and run it in SSMS. Its quite possible that it actually doesn't return any records. It is not necessary to build this in a stored procedure to return records but putting this into a SP or view will allow you to move a lot of messy SQL out of your application and also assist with unit testing. — Nick.Mc, Sep 02 '15 at 07:30
Thanks Nick. Sorry I'm having trouble getting the syntax correct above. I did run the query in the SSMS and it returns records as expected. — user3889026, Sep 02 '15 at 14:20
I corrected the sql string above so it more closely matches an actual example from the app. — user3889026, Sep 02 '15 at 14:26
Take a look at this: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=33 Some recordset types don't populate the `RecordCount` property (`adOpenKeyset` should though). What happens if you use `While Not .EOF and .BOF` instead? What is that actual value of `RecordCount` in your code? — Nick.Mc, Sep 02 '15 at 21:44
Nick. You are the man. Thanks. Your last comment is correct. The records were returned it was just the RecordCount property was not set. Using While Not .EOF and .BOF is working. Thanks! — user3889026, Sep 05 '15 at 03:59

score 1 · Accepted Answer · answered Sep 06 '15 at 01:46

You're only checking RecordCount. Take a look at this: slxdeveloper.com/page.aspx?action=viewarticle&articleid=33 Some recordset types don't populate the RecordCount property (adOpenKeyset should though). What happens if you use While Not .EOF and .BOF instead? What is that actual value of RecordCount in your code?

score 0 · Answer 2 · answered Sep 01 '15 at 02:04

Would it at all be possible to run queries from SQL which save to an access file? I have nothing but trouble pulling data directly into access. I do have success when I set up an ODBC database and go to data -> get external data -> from other sources -> From microsoft query

Another method I myself have been successful with is using the power query add-on from microsoft. https://www.microsoft.com/en-us/download/details.aspx?id=39379

Despite this, what I still mostly end up doing is using the SQL import/export tool. I don't have screenshots or specific instructions as I'm not at work right now, but this can write directly to text files, Access databases, everything. I love it too much. Getting the correct drivers was a loop for sure. If you're on 64-bit and having issues then this is the driver you need. http://www.microsoft.com/en-us/download/details.aspx?id=13255 What I do is:

Set up my source (SQL 11 native client), choose the database you're pulling from
Specify an outfile type and location (I think it has to already exist)
When prompted to specify whether to pull data from tables and views or write a query, select write a query.
Go through the rest of the importer, you can edit the sql statement later on when viewing conversion and specify whether the transfer fails or ignores errors etc.

I personally still use the import export tool for transfers of all sizes because it's just so difficult to get all the correct drivers and get SQL to like what I want. (and without admin rights I get tired of asking my boss).

I hope one of those solutions can help you!

score 0 · Answer 3 · edited May 23 '17 at 12:25

I've outline a more proper fix and a quick fix...

The more proper fix is the Data Layer Pattern. There is a lot to this fix and it may require some application structural changes. This is discussed in depth in another question: Data Access Layer design patterns

A very simple fix is to use Access Linked tables. A Linked Table works like a normal Access table except the data is stored and updated on the SQL Server. Its basically a built in Data Access Layer to SQL Server. Its not an elegant solution but it gets you up and running right away. More info can be found here: https://support.office.com/en-us/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-77bc6089b84e#bm2

On thing to be aware of with Linked Tables are that some Access Queries and Forms retrieve all the records before filtering and can lock the table so you can end up with some performance headaches if you have lots of data and lots of users.

score 0 · Answer 4 · answered Sep 02 '15 at 02:11

0

Consider using the SQL server SYNONYM feature to add aliases for objects in one database to the other. Then just update all your queries to use one database.

Also, you could merge the two databases with each one, or one of them, going into a new schema to keep them separate. This could be tough if you have a lot of stored procedures, views, and functions in the database. This may be a terrible answer, but it could also be true that the two databases should never have been separate in the first place.

answered Sep 02 '15 at 02:11

ErikE

48,881
23
151
196

Thanks for this tip. I will research this some more. I can't merge the 2 databases into one since one of them is an off the shelf ERP system that happens to run on SQL Server. But both dbs live on the same server. – user3889026 Sep 02 '15 at 14:29

score 0 · Answer 5 · edited Apr 13 '17 at 12:42

In the INNER JOIN, you prefixed the table name with DatabaseName.Schema.:

... FROM TABLE_DB1 INNER JOIN [DB2].[dbo].TABLE_DB2 ...

But you didn't do it in the other places where TABLE_DB2 occurs.

So you either need to change this:

ON (TABLE_DB1.Comp_Item_No = TABLE_DB2.item_no) AND (TABLE_DB1.Loc = TABLE_DB2.loc)

...to this:

ON (TABLE_DB1.Comp_Item_No = [DB2].[dbo].TABLE_DB2.item_no) AND (TABLE_DB1.Loc = [DB2].[dbo].TABLE_DB2.loc)

Or (which I prefer) you can use aliases for the table names in the FROM clause:

... FROM TABLE_DB1 t1 INNER JOIN [DB2].[dbo].TABLE_DB2 t2...

...then you use the aliases everywhere else:

str = "SELECT t1.Parent_Item_No FROM TABLE_DB1 t1 INNER JOIN [DB2].[dbo].TABLE_DB2 t2 ON (t1.Comp_Item_No = " & _
            "t2.item_no) AND (t1.Loc = t2.loc) " & _
            "GROUP BY t1.Parent_Item_No " & _
            "HAVING (((t1.Parent_Item_No)='" & str_Assembly & "'));"

Additional background information:

If you connect to an SQL Server via ADO, you're directly connecting to exactly one database - the one in the connection string:

.ConnectionString = "Server=[MY_SERVER];Database=[MY_DATABASE];User Id=sa; Password=password;"

So in your case, the database you're connecting to is named MY_DATABASE. Any SQL you're executing via ADO goes to that database.

If you need to get data from other databases on the same server, you need to prefix the names with DatabaseName.Schema. in all places where you use them.

So let's assume we have:

a table MY_TABLE in MY_DATABASE
a table OTHER_TABLE in OTHER_DATABASE on the same server
both tables have the schema dbo (the default in SQL Server)

With the connection string from above (connecting to MY_DATABASE), you can join them as follows:

select *
from MY_TABLE 
  inner join OTHER_DATABASE.dbo.OTHER_TABLE
  on MY_TABLE.SomeColumn = OTHER_DATABASE.dbo.OTHER_TABLE.OtherColumn
where OTHER_DATABASE.dbo.OTHER_TABLE.EvenAnotherColumn = 'foo'

See? Everywhere I used OTHER_TABLE, I prefixed it with OTHER_DATABASE.dbo..

PS:
It's bad practice to use the sa user to connect to a database with an application. The sa user has the highest permissions possible.
You should either use Windows authentication or create a dedicated SQL user for your app.

Thanks Christian. The reason I only used the [db].[dbo] on the join is because when I run the query that way directly in the SSMS it works. So I assumed this was ok. But you are saying that I need to use the db and schema or an alias everywhere (not just when joining). I will try it but figured if it worked in SSMS than that was not the issue. — user3889026, Sep 02 '15 at 15:50
I don't have access to SQL Server or SSMS right now *(I wrote all the code in my post from the top of my head)*, but I'm quite sure that in SSMS, you need to use db/schema/alias **everywhere** as well. — Christian Specht, Sep 02 '15 at 15:53

score 0 · Answer 6 · answered Sep 02 '15 at 17:08

0

Consider storing your SQL in a pass-through query instead of VBA code. You can apply your filter using a copy of the .sql property of the pass-through query's querydef object, modifying it with the criteria they enter in your form at runtime.

answered Sep 02 '15 at 17:08

Beth

9,531
1
24
43

ms access connect to 2 sql server databases on the same server

6 Answers6

Additional background information: