Using ADO to import Data

[jo15765]

I am trying to modify some code that I have been using to take an array and hence cut down on the amount of code that I have. Below is what I am trying to use:
[vba]
Public Sub TryME()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim wb As Workbook
Dim uRp
Dim qryName
Dim sDate As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String

uRp = Array("18653", "22481", "48625")
qryName = Array("qry_number_one", "qry_number_two")

Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\" & uRp & "\\" & uRp & "_test.mdb;"

For q = LBound(uRp) To UBound(uRp)
On Error Resume Next
cn.Execute qryName
Next q

End Sub
[/vba]

I get a Run Time Error 13
Type Mismatch

on the cn.Open line of code. It is the correct address, but I don't know why it is erroring. Can someone point out what my issue is? Thanks

[Kenneth Hobs]

When you build the open string, you are using the array uRp as a string. Another problem is the use of "\\". If you have multiple MDB files, then you need and array in a loop as well.

[jo15765]

When you build the open string, you are using the array uRp as a string.

So I need to convert that array from variant to string and then it will be able to process the location?

Another problem is the use of "\\". If you have multiple MDB files, then you need and array in a loop as well.

How would I add in an array to do this? I can't wrap my mind around this element?

[Kenneth Hobs]

You will have a loop within a loop. Of course you need to close your connections after you process an open connection depending on what you are doing.

Here is an illustration shown with MsgBox since I don't have your MDB files or other details. I used two methods for the For loops. You can use either or both methods as you like.

Notice how I made a string for the Open command. You can then just use cn.Open sOpen or use debug.print sOpen if you need to see what is going on.

[vba]Public Sub TryME()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim wb As Workbook
Dim uRp() As Variant
Dim qryName() As Variant
Dim sDate As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String

Dim sOpen As String, q As Variant, qq As Variant
uRp = Array("18653", "22481", "48625")
qryName = Array("qry_number_one", "qry_number_two")

For q = LBound(uRp) To UBound(uRp)
For Each qq In qryName()
sOpen = "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\" & uRp(q) & "\\" & uRp(q) & "_test.mdb;"
'MsgBox uRp(q), vbInformation, qq
MsgBox sOpen, vbInformation, qq
Next qq
Next q
End Sub[/vba]

[jo15765]

my mdb names and locations are:
18653_test.mdb R:\18653\\18653_test.mdb;
22481_test.mdb R:\22481\\22481.mdb;
48625_test.mdb R:\48625\\48625.mdb;"

These three databases will hold either qry_number_one OR qry_number_two (I just realized I need to add an On Error Resume Next statement) and what I am trying to accomplish with this code is instead of writing a ADO import procedure for each uRp (at times there can be about 40), I was trying to create an array, that will open the connection, copy the data from the query, and paste it into the workbook --- this is the code I am using in the individual modules to copy in:
[vba]
Sheets(2).Rows(3).CopyFromRecordset .DataSource
[/vba]

From your code, would I just need to run this (I have this in my VBE and it is not throwing a compile error):
[vba]
For q = LBound(uRp) To UBound(uRp)
For Each qq In qryName()
sOpen = "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\" & uRp(q) & "\\" & uRp(q) & "_test.mdb;"
'//telling Excel where to copy the data to and where to copy from
Sheets(2).Rows(3).CopyFromRecordset .DataSource
Next qq
'//Close the connection
s.Close
Next q
[/vba]

[Kenneth Hobs]

You have several issues with that code. Where is your Open? Where is your Execute?

IF you are using a stored and named query, you should probably use QueryTables. See the routine InsertTableWithStoredSQL:

http://www.vbaexpress.com/forum/showthread.php?t=24118
http://www.vbaexpress.com/forum/showthread.php?t=24575
http://www.vbaexpress.com/forum/showthread.php?t=23783
http://www.vbaexpress.com/forum/showthread.php?t=26145

[jo15765]

You have several issues with that code. Where is your Open?

I was trying to modify the code you posted above to work. I thougth my open was the sOpen statement? Now looking at the code I have posted, I am trying to run a query then open a database, which wouldn't work. I would need the sOpen statement to come 1st.

Where is your Execute?

The execute...I had been using

Sheets(2).Rows(3).CopyFromRecordset .DataSource

To run the query, that gave it location as well as copied in the data, I was hoping something similar would work in this scenario, but it doesn't look like it is going to. I will try a modification to my code of:

For q = LBound(uRp) To UBound(uRp) 
    sOpen = "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\" & uRp(q) & "\\" & uRp(q) & "_test.mdb;"
    For Each qq In qryName() 
         '//telling Excel where to copy the data to and where to copy from
        Sheets(2).Rows(3).Select
        sExecute
    Next qq 
Next q 
'//Close the connection
s.Close

As far as the SQL Server goes, the tables are housed in SQL Server but linked into an access database, that's why I was just trying to run an access query that will update the linked table.