One recordset from 3 databases using ADO from Excel

[mattdrinks]

Hi everyone,

I have many different Access databases from which I need to get information onto an Excel sheet. I would like to do this using ADO so users can select options and run the code from a UserForm

My problem is creating the SQL string for the ADO command object. So far looking at Thread705-567768 I have come up with this code:

Sub MultiConnection()

'---DECALRE---
Dim strDBMvpr As String, strDBStatic As String
Dim strConnDBMvpr As String, strConnDBStatic As String
Dim cnnDBMvpr As ADODB.Connection, cnnDBStatic As ADODB.Connection
Dim strSQL As String
Dim cmdSQL As ADODB.Command
Dim rstProducts As ADODB.Recordset

'---LOAD---
'loads database locations from excel sheet
strDBMvpr = shtOptions.Cells(2, 2)
strDBStatic = shtOptions.Cells(3, 2)

'---SET---
strConnDBMvpr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBMvpr
strConnDBStatic = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBStatic
'set objects
Set cnnDBMvpr = New ADODB.Connection
Set cnnDBStatic = New ADODB.Connection
Set cmdSQL = New ADODB.Command
Set rstProducts = New ADODB.Recordset
'Set connections to databases
cnnDBMvpr.ConnectionString = strConnDBMvpr
cnnDBStatic.ConnectionString = strConnDBStatic

'---OPEN---
'open connections
cnnDBMvpr.Open
cnnDBStatic.Open

'---BUILD---
'build SQL string
strSQL = "SELECT Mvpr.Prefix, Product.KeyCode, Product.Desc, Mvpr.A2, Product.PG, Product.Range, Product.PSupp, Mvpr.SubKey2, Mvpr.A12, Product.Cind, Product.Info " & _
        "FROM cnnDBStatic.Product LEFT OUTER JOIN cnnDBMvpr.Mvpr ON Product.KeyCode = Mvpr.SubKey1 " & _
        "WHERE (Product.PSupp=""DRAPER"") AND (Mvpr.Prefix=""C"")"

'---EXECUTE---
'execute commands
cmdSQL.CommandType = adCmdText
cmdSQL.CommandText = strSQL
cmdSQL.ActiveConnection = cnnDBStatic
rstProducts.Open cmdSQL

'---OUTPUT---
Do While rstProducts.EOF = False

Loop
Application.ScreenUpdating = False
Workbooks.Add
Application.ScreenUpdating = True

'---CLOSE---
rstProducts.Close
cnnDBMvpr.Close
Set rstProducts = Nothing
Set cnnDBMvpr = Nothing
End Sub
[\CODE]

I have changed the ActiveConnection property from CurrentProject.ActiveConnection to cnnDBStatic because CurrentProject is an Access Application object and I can not find the same object in excel.  Does anyone know of an Excel equivalant for CurrentProject.ActiveConnection?

The current code stops at:
[CODE]
rstProducts.Open cmdSQL

with the following error message:
Could not find file 'C:\My Documents\Macros\cnnDBStatic.mdb'
I am not sure where it has got this reference from the location of my databases are:
\\RUDH_SERVER\AutoPart\VTFiles\Mvpr.mdb
\\RUDH_SERVER\AutoPart\VTFiles\Static.mdb
and are these are read from the Excel sheet.

Does anyone know how I can create a ADO recordset that gets its data from different Access Databases? Two to start with but next I need to use three.

Thanks Matt

 

[SkipVought]

Hi,

If you're stuck on the SQL, then try this, using ' instead of "

"SELECT Mvpr.Prefix, Product.KeyCode, Product.Desc, Mvpr.A2, Product.PG, Product.Range, Product.PSupp, Mvpr.SubKey2, Mvpr.A12, Product.Cind, Product.Info " & _
        "FROM cnnDBStatic.Product LEFT OUTER JOIN cnnDBMvpr.Mvpr ON Product.KeyCode = Mvpr.SubKey1 " & _
        "WHERE (Product.PSupp='DRAPER') AND (Mvpr.Prefix='C')"

hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mattdrinks]

Hi Skip,

I have just tried replacing the " with ' and it has unfortunately made no difference.

I think the problem is with the connections and how I am using them in the SQL statement. I am sure it is possible as cmmrfrds has a function in Thread705-567768 that does exactly what I am trying to do. I just can not seem to get my code to work.

Do you have any ideas what I am doing wrong?