Modify VBA code to look at multiple SQL databases.

[pghquest]

ok I'm stuck. I have a SQL query that when I run through SQL, it works fine. It queries 2 separate database tables and finds ones in 1 database, not in the other, and then counts them.

The code below, I copied from another part of my vba which works, and then modifies, but that only looks at one database..

I'm having errors around the Dim rst s DAO.Recordset, I'm assuming its because of the multiple databases, (I'm new with VBA so please be kind).

How do I modify the code below to get it to work?

Public Function GetQueryCountG01A() As Long
CurrentDb.QueryTimeout = 0
Dim rst As DAO.Recordset
Dim sql As String

sql = "SELECT COUNT(*) / 1000 + 1 AS total " _
& "FROM [Database1].[dbo].[Product] RIGHT OUTER JOIN " _
& "[Database2].[dbo].[Inventory] ON [database1].[dbo].[Product].Sku = [database2].[dbo].[Inventory].LocalSKU " _
& "WHERE ([database2].[dbo].[Inventory].ItemName <> 'x') " _
& "AND ([database2].[dbo].[Inventory].Price9 IS NOT NULL) " _
& "AND ([database2].[dbo].[Inventory].RetailPrice <> 0) " _
& "AND ([database2].[dbo].[Inventory].RetailPrice IS NOT NULL) " _
& "AND ([Database2].[dbo].[Inventory].Weight IS NOT NULL) " _
& "AND ([database2].[dbo].[Inventory].Discontinued = 0) " _
& "AND ([database2].[dbo].[Inventory].Category = 'Books - new') " _
& "AND ([Database1].[dbo].[Product].Sku IS NULL) "
Set rst = CurrentDb.OpenRecordset(sql)
With rst

If Not .EOF Then
GetQueryCountG01A = .Fields(0).Value
End If
.Close
End With

Set rst = Nothing
End Function

 

[pghquest]

btw, when I hit the debug button, this line is whats in yellow

Set rst = CurrentDb.OpenRecordset(sql)

 

[pbaldy]

Try this technique:

http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

Oh, are the tables linked? You can't use the 3-part syntax from Access, only in t-sql.