Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP linking access table to excel | Excel Worksheet Functions | |||
Import Access Table into Excel | Excel Worksheet Functions | |||
How to retrieve the value within the table from Access into Excel? | Excel Discussion (Misc queries) | |||
Data from Excel to Access Table | Excel Discussion (Misc queries) | |||
Excel Pivot Table with Access | Excel Discussion (Misc queries) |