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" <(E-Mail Removed)> wrote in message
news:0AC2B41A-881D-4CC6-8505-(E-Mail Removed)...
>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.
|
Similar Threads
