Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i write data from Excel to a table in Access (2007)?
I have an Access 2007 database, which I want to write different sets of data
into. The datawill come from a web app in tab delimited or XML files, and will require some reformatting in Excel prior to adding to Access, for example the column headers in the tab delimited file become records in a table. I therefore need to open my access database and the correct table within it, and append the data, using VBA. How is this done? I can find plenty of reference to taking data in the opposite direction, but nothing on writing to Access. -- Nigel Legg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i write data from Excel to a table in Access (2007)?
Look at the Access VBA help or get an Access VBA Book. Once you know the
syntax of the Access commands it is easy to use in Excel. You just have to declare an access object like obj = CreateObject("Access.Application") and in Excel in the menu Tools -= References declare the Microssoft Access Object and depending on the methods you use declare a reference to a Micorsoft ActiveX Data Object (ADO). "Nigel Legg" wrote: I have an Access 2007 database, which I want to write different sets of data into. The datawill come from a web app in tab delimited or XML files, and will require some reformatting in Excel prior to adding to Access, for example the column headers in the tab delimited file become records in a table. I therefore need to open my access database and the correct table within it, and append the data, using VBA. How is this done? I can find plenty of reference to taking data in the opposite direction, but nothing on writing to Access. -- Nigel Legg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i write data from Excel to a table in Access (2007)?
Option Explicit
Private Sub saveDataToAccess() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim r As Integer r = 6 'Use for Access (Ace No Security) strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;" _ & "Persist Security Info=False;" 'Use for Access (Ace With Security) 'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;" _ '& "Jet OLEDB:Database Password=MyDbPassword;" 'sSQL = Name Of Your Access table Change to your 'Table Name sSQL = "TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Field1") = Range("A" & r).Value .Fields("Field2") = Range("E" & r).Value .Fields("Field3") = Range("F" & r).Value .Fields("Field4") = Range("G" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close cnn.Close End Sub "Nigel Legg" wrote: I have an Access 2007 database, which I want to write different sets of data into. The datawill come from a web app in tab delimited or XML files, and will require some reformatting in Excel prior to adding to Access, for example the column headers in the tab delimited file become records in a table. I therefore need to open my access database and the correct table within it, and append the data, using VBA. How is this done? I can find plenty of reference to taking data in the opposite direction, but nothing on writing to Access. -- Nigel Legg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i write data from Excel to a table in Access (2007)?
Mike, thanks for this; I will try it out. I did not get very far with the
automation method as posted by Jake; I know a hell of a lot more about Access than Excel (hence wanting to transfer the data there), this should get me going again. Cheers. -- Nigel Legg "Mike" wrote: Option Explicit Private Sub saveDataToAccess() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim r As Integer r = 6 'Use for Access (Ace No Security) strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;" _ & "Persist Security Info=False;" 'Use for Access (Ace With Security) 'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;" _ '& "Jet OLEDB:Database Password=MyDbPassword;" 'sSQL = Name Of Your Access table Change to your 'Table Name sSQL = "TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Field1") = Range("A" & r).Value .Fields("Field2") = Range("E" & r).Value .Fields("Field3") = Range("F" & r).Value .Fields("Field4") = Range("G" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close cnn.Close End Sub "Nigel Legg" wrote: I have an Access 2007 database, which I want to write different sets of data into. The datawill come from a web app in tab delimited or XML files, and will require some reformatting in Excel prior to adding to Access, for example the column headers in the tab delimited file become records in a table. I therefore need to open my access database and the correct table within it, and append the data, using VBA. How is this done? I can find plenty of reference to taking data in the opposite direction, but nothing on writing to Access. -- Nigel Legg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i write data from Excel to a table in Access (2007)?
On Sat, 21 Feb 2009 04:08:01 -0800, Nigel Legg
<nigel.legg.at.katugas-research-services.co.uk wrote: I have an Access 2007 database, which I want to write different sets of data into. The datawill come from a web app in tab delimited or XML files, and will require some reformatting in Excel prior to adding to Access, for example the column headers in the tab delimited file become records in a table. I therefore need to open my access database and the correct table within it, and append the data, using VBA. How is this done? I can find plenty of reference to taking data in the opposite direction, but nothing on writing to Access. Nigel: For this, I use ADO. Specifically I use the Execute method of the Connection object sqlInsert = "INSERT INTO tblTable.... etc." adoConn.Execute sqlInsert Here's some other resources: http://www.dailydoseofexcel.com/arch...do-parameters/ http://www.dailydoseofexcel.com/arch...tables-in-vba/ http://www.dailydoseofexcel.com/arch...ordset-basics/ http://www.dailydoseofexcel.com/arch...essons-in-sql/ Note that while the Execute method is the easiest way, everyone tells me I should be using the Command object and Parameters objects. If you're already familiar with ADO, you may want to look into those objects. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Access 2007 Table Using Excel 2007 VBA | Excel Programming | |||
Write data to Access table with INSERT when table has auto number | Excel Programming | |||
Write data to access file through EXCEL | Excel Programming | |||
Write data to access file through EXCEL | Excel Programming | |||
Write data to access file through EXCEL | Excel Programming |