I am trying to automate import of an Access Table into Excel for further use.
I have tried the code below from a previous post but get an immediate error
on the first 2 Dim statements. The Database is called "Pressing Database.mdb"
and the table is called "Used Felt". Ihave only listed 4 of the columns for
brevity but there are many more.
Thanks for any help
Steve

Private Sub QueryAccessDB()

Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer

i = 1

'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=\\Jns-dc01\kandbreplica\" _
& "Pressing Database.mdb;Persist Security Info=False"
MyTable = "Used Felt"
'Use for jet
strSQL1 = "SELECT MyTable.MillCode, MyTable.Machine, MyTable.Position ,
" _
& "MyTable.PositionName, " _
& "FROM MyTable; "


Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!MillCode
Sheets("Sheet1").Range("B" & i) = rs1!Machine
Sheets("Sheet1").Range("C" & i) = rs1!Position
Sheets("Sheet1").Range("D" & i) = rs1!PositionName
' Sheets("Sheet1").Range("E" & i) = rs1!FIELDNAME5
' Sheets("Sheet1").Range("F" & i) = rs1!FIELDNAME6
' Sheets("Sheet1").Range("G" & i) = rs1!FIELDNAME7
' Sheets("Sheet1").Range("H" & i) = rs1!FIELDNAME8
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub