VBA To Transfer from Excel into Access: Directory Issue

[BigxRed]

This is the first time I am attempting to write in VBA. I had viewed a few YouTube tutorials on how to transfer data from an Excel spreadsheet into an Access database. There were several videos on this topic and most generated similar code. I am aware that Access 2007 already has this feature built in, but I need to have this to work with Access 2003. This is the code that I have been working with:

Private Sub CommandButton1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.ConnectionString = "DBQ=J:\ReservesA\DataBases\ReserveDatabase.mdb;" & "Driver=(Microsoft Access Driver (*.mdb));"

con.Open

Set rs.ActiveConnection = con

rs.Open "Select ' from test_db"

startRow = 3

Do Until rs.EOF
Cells(startRow, 4) = rs.Fields(0).Value
rs.MoveNext
startRow = startRow + 1
Loop

rs.Close
Set rs = Nothing
con.Close
Set con = Nothing

End Sub

I believe the source of the problem is within this line of code:

con.ConnectionString = "DBQ=J:\ReservesA\DataBases\ReserveDatabase.mdb;" & "Driver=(Microsoft Access Driver (*.mdb));"

The Access database is located on a network drive. The network drive is named "minos", so I believe the proper directory for the database would be:

\\minos\J:\ReservesA\DataBases\ReserveDatabase.mdb

However, even when I input the directory this way, I still receive the same error message. I cannot recall the exact error message, but it stated that the Access database could not be located. Any help would be greatly appreciated. I do not understand the majority of the VBA code.

 

[PHV]

Why not simply use a QueryTable(MS-Query) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Check out...

http://www.connectionstrings.com/

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]