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

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

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

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

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

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

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

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