| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to access 2007
I am trying to export spreadsheet data from excel to access and executed from
within excel; below is a VBA script working with access 2003 but not with '07 I am assuming the problem is with DAO Object Library Does anybody now what the problem is or possible work around could be? Thanks Sebastian Sub ExportToAccessGateway() ' Check in VBE Tools-References-Microsoft DAO Object Library Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\0\Access\Production\ProductionDB. accdb") Set rs = db.OpenRecordset("Gateway", dbOpenTable) r = 1 Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then With rs .AddNew .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() 'add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing db.Close Set db = Nothing |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
Not sure but give this a wirl using ADO
Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
no luck
It has a problem with ADODB "Mike" wrote: Not sure but give this a wirl using ADO Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
What Reference do you have
"Sebastian" wrote: no luck It has a problem with ADODB "Mike" wrote: Not sure but give this a wirl using ADO Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
I have:
Visual Basic For Applications Microsoft Excel 12.0 Object Library OLA Automation Microsoft Access 12.0 Object Library Microsoft Office 12.0 Object Library Microsoft DAO 3.6 Object Library Microsoft ADO Ext. 2.8 for DDL and Security "Mike" wrote: What Reference do you have "Sebastian" wrote: no luck It has a problem with ADODB "Mike" wrote: Not sure but give this a wirl using ADO Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
Try Microsoft ActiveX Data Objects 2.8 Library
"Sebastian" wrote: I have: Visual Basic For Applications Microsoft Excel 12.0 Object Library OLA Automation Microsoft Access 12.0 Object Library Microsoft Office 12.0 Object Library Microsoft DAO 3.6 Object Library Microsoft ADO Ext. 2.8 for DDL and Security "Mike" wrote: What Reference do you have "Sebastian" wrote: no luck It has a problem with ADODB "Mike" wrote: Not sure but give this a wirl using ADO Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
I am getting Run-time error
"Unrecognized database format..." "...***.accdb" This is converted 03 database to 07 format "Mike" wrote: Try Microsoft ActiveX Data Objects 2.8 Library "Sebastian" wrote: I have: Visual Basic For Applications Microsoft Excel 12.0 Object Library OLA Automation Microsoft Access 12.0 Object Library Microsoft Office 12.0 Object Library Microsoft DAO 3.6 Object Library Microsoft ADO Ext. 2.8 for DDL and Security "Mike" wrote: What Reference do you have "Sebastian" wrote: no luck It has a problem with ADODB "Mike" wrote: Not sure but give this a wirl using ADO Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
exporting to access 2007
I will be away for next 7 days and I would like to resume working on this on
30ieth. In the meantime good old vbs does the job. Sebastian "Sebastian" wrote: I am getting Run-time error "Unrecognized database format..." "...***.accdb" This is converted 03 database to 07 format "Mike" wrote: Try Microsoft ActiveX Data Objects 2.8 Library "Sebastian" wrote: I have: Visual Basic For Applications Microsoft Excel 12.0 Object Library OLA Automation Microsoft Access 12.0 Object Library Microsoft Office 12.0 Object Library Microsoft DAO 3.6 Object Library Microsoft ADO Ext. 2.8 for DDL and Security "Mike" wrote: What Reference do you have "Sebastian" wrote: no luck It has a problem with ADODB "Mike" wrote: Not sure but give this a wirl using ADO Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\0\Access\Production\" _ & "ProductionDB.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 1 ' the start row in the worksheet Do While Len(Range("D" & r).Formula) 0 If Range("M" & r).Value = "Filled" Then ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Symbol") = Range("D" & r).Value .Fields("Type") = Range("F" & r).Value .Fields("Account#") = Range("R" & r).Value .Fields("CreationDate") = Now() ' add more fields if necessary... .Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| exporting worksheet to access | Excel Discussion (Misc queries) | |||
| Exporting from Access | Excel Worksheet Functions | |||
| Exporting to excel from Access | Excel Programming | |||
| Exporting from Access to Excel | Excel Programming | |||
| Exporting data to access | Excel Programming | |||
Linear Mode
