mikeym82


I have a mdb file called the CustomReportsModule.mdb, and a Data.mdb file.

I am trying to create a module within the Customom Reports Module to connect to the data.mdb (which is used only for data storage), and execute a select query. Then I want to create a table based on the query's results.

I've been trying to use ADODB, but can't seem to really wrap my head around what to do. I have been searching the net, but haven't found what I think I'm looking for.

Any help is appreciated -Thanks!





Re: Making connection from one Access database to another.

mikeym82


I found my own answer, but can't remember where exactly. I know I found some in the help files in the visual basic editor, but also found some on the net when I searched for "adodb.connection recordset".

Anyhow, I hope this helps anyone in my situation. In the future I plan on making this a function where I just pass some variables in, but this is the start that I needed.

Dim cnnConn As ADODB.connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:Database Password") = "blahpasswordblah"
.Open "C:\Databases\MyDatabase.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "SELECT * FROM AddressBook"
.CommandType = adCmdText
.Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing







Re: Making connection from one Access database to another.

leo romero

Mike,

I has a specific question in regards to the Set CMD usage and wanted your opinion on something.

Leo Romero

PDSN