Access Table into Excel using VB
 

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

Access Table into Excel using VB
 

Hi,

Before I look at the code issues, why not just create a live connection to
Access and click the Refresh Data button whenever you want the current data?

Here are the steps in 2003 for creating a connection to Microsoft Access:

1. Choose Data, Import External Data, New Database Query
2. Choose MS Access Database and click OK
3. Navigate to you Access database, select it and click OK
4. Find the table or query you want to connect to (it is possible to do a
multitable query here by picking tables that are linked)
5. Click the + sign for the table or query. This will display all the
fields (columns) in that table.
6. Double-click each field you want or click the while selecting the table
name, if you want all the fields.
7. Click Next. This screen allows you to filter the record being returned
before they reach Excel.
8. Click Next. This screen allows you to sort the data before returning it
to Excel, probably little point in doing this.
9. Click Next. Choose Return data to Microsoft Office Excel and click Finish.
10. Choose a location for the linked data and click OK.

To refresh the data either choose Data, Refresh Data or click the Refresh
All button if you have it displayed.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

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

Access Table into Excel using VB
 

Works like a charm
Thanks a bunch
Steve

"Shane Devenshire" wrote:

Hi,

Before I look at the code issues, why not just create a live connection to
Access and click the Refresh Data button whenever you want the current data?

Here are the steps in 2003 for creating a connection to Microsoft Access:

1. Choose Data, Import External Data, New Database Query
2. Choose MS Access Database and click OK
3. Navigate to you Access database, select it and click OK
4. Find the table or query you want to connect to (it is possible to do a
multitable query here by picking tables that are linked)
5. Click the + sign for the table or query. This will display all the
fields (columns) in that table.
6. Double-click each field you want or click the while selecting the table
name, if you want all the fields.
7. Click Next. This screen allows you to filter the record being returned
before they reach Excel.
8. Click Next. This screen allows you to sort the data before returning it
to Excel, probably little point in doing this.
9. Click Next. Choose Return data to Microsoft Office Excel and click Finish.
10. Choose a location for the linked data and click OK.

To refresh the data either choose Data, Refresh Data or click the Refresh
All button if you have it displayed.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

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