G
Guest
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.
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.