I recently converted my database to .accdb format. I forgot however, that I
had code in an excel file that opened the database and created and entered
info in an existing table under a new record. The code no longer works. I
tried changing the hard coded file name to .accdb and got a new error saying
that the database format was not recognized. Is there an easy way to change
the format back (I think it was 2003) or a simple code fix that I can make? I
have around 10 different excel files that do this same code so I will fix all
of them if reverting to the old format is not an option. Here is the code so
you will know better what I am talking about:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=S:\Time Clock\NJC.accdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Now()
Select Case Range("D3").Value
Case 1
.Fields("Company") = "Five Star Ford"
Case 2
.Fields("Company") = "Tom Jones Ford"
Case 3
.Fields("Company") = "Courtesy Chevrolet"
End Select
If Range("D2") 10000 Then
.Fields("Description") = "Service Body and
Options for APS veh# " & Range("D2").Value & "."
Else
.Fields("Description") = "Service Body and
Options for APS veh# 0" & Range("D2").Value & "."
End If
.Fields("HourlyCost") = 60
.Fields("HourlyPrice") = 80
.Fields("Status") = "C"
.Fields("EstimateTot") = Range("E62").Value
If Range("D2") 10000 Then
.Fields("Link") = Path & Left(Range("D2").Value,
2) & " Series\" & Range("D2").Value & " Service Body " & Range("B1").Value &
".xls"
Else
.Fields("Link") = Path & "0" &
Left(Range("D2").Value, 1) & " Series\0" & Range("D2").Value & " Service Body
" & Range("B1").Value & ".xls"
End If
' add more fields if necessary...
.Update ' stores the new record
End With
Range("D1").Value = rs.Fields("JobNumber")
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks for any help you can give me with this.

In order to use ADO to connect to *.accdb file, you cannot use existing MS
OLEDB Jet provider, because Access2007 uses new database engine, instead of
Jet. You need to download MS Office Access database engine, if the Excel app
runs on a computer without Access2007 installed. Not like Jet engine, which
comes with all Windows version so far, the new Access database engine only
installed with Access2007. If you need to access *.accdb without Access2007
installed, you need to download and install the new engine. I could not
remember if you need to change the ConnectionString, and also do not
remember the download location. Search MS site should get you there easily.


"JCanyoneer" wrote in message
...
I recently converted my database to .accdb format. I forgot however, that I
had code in an excel file that opened the database and created and entered
info in an existing table under a new record. The code no longer works. I
tried changing the hard coded file name to .accdb and got a new error
saying
that the database format was not recognized. Is there an easy way to
change
the format back (I think it was 2003) or a simple code fix that I can
make? I
have around 10 different excel files that do this same code so I will fix
all
of them if reverting to the old format is not an option. Here is the code
so
you will know better what I am talking about:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=S:\Time Clock\NJC.accdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Now()
Select Case Range("D3").Value
Case 1
.Fields("Company") = "Five Star Ford"
Case 2
.Fields("Company") = "Tom Jones Ford"
Case 3
.Fields("Company") = "Courtesy Chevrolet"
End Select
If Range("D2") 10000 Then
.Fields("Description") = "Service Body and
Options for APS veh# " & Range("D2").Value & "."
Else
.Fields("Description") = "Service Body and
Options for APS veh# 0" & Range("D2").Value & "."
End If
.Fields("HourlyCost") = 60
.Fields("HourlyPrice") = 80
.Fields("Status") = "C"
.Fields("EstimateTot") = Range("E62").Value
If Range("D2") 10000 Then
.Fields("Link") = Path &
Left(Range("D2").Value,
2) & " Series\" & Range("D2").Value & " Service Body " & Range("B1").Value
&
".xls"
Else
.Fields("Link") = Path & "0" &
Left(Range("D2").Value, 1) & " Series\0" & Range("D2").Value & " Service
Body
" & Range("B1").Value & ".xls"
End If
' add more fields if necessary...
.Update ' stores the new record
End With
Range("D1").Value = rs.Fields("JobNumber")
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks for any help you can give me with this.