Having a problem opening adding a record to an Access .accdb

[Bob Ully]

I am new to using Excel VBA's with Access. I am trying to open an Acess .accdb file, and I get the error "Unrecognized Database Format". I am using Excel 2007, and I checked off Active X Data Oject 2.8 Library, under tools, references. Am I using the correct library ? Thanks. Bob Ully

 

[xenou]

Hi,
To use ADO with the newer Access file format you need ADO 6.0 at least (I think) or ADO 6.1, which is what I have.
ξ

 

[Bob Ully]

Hi,
To use ADO with the newer Access file format you need ADO 6.0 at least (I think) or ADO 6.1, which is what I have.
ξ

I am using VBA thru Excel, I which library are you using ? Or Where do I check the ADO version ? Thanks. Bob U.

 

[xenou]

I am new to using Excel VBA's with Access. I am trying to open an Acess .accdb file, and I get the error "Unrecognized Database Format". I am using Excel 2007, and I checked off Active X Data Oject 2.8 Library, under tools, references. Am I using the correct library ? Thanks. Bob Ully

The same way you did this . In the VBA editor go to to Tools | References and check the box for Microsoft ActiveX Data Objects 6.0 Library (or higher). Uncheck the 2.8 version if you still have that checked.

There are various different ways to use VBA to connect to access. If you are having trouble post your code as that will enable us to be more specific.

 

[HiTechCoach]

I am new to using Excel VBA's with Access. I am trying to open an Acess .accdb file, and I get the error "Unrecognized Database Format". I am using Excel 2007, and I checked off Active X Data Oject 2.8 Library, under tools, references. Am I using the correct library ? Thanks. Bob Ully

Besides the correct library you all need to specify the correct driver in the Provider section of your connection string.

What is the Provider set to in your connection string? Are you using the ACE driver?

See: Access 2007 Connection String Samples - ConnectionStrings.com

 

[Bob Ully]

The "Ace" driver, did the trick. I still have another issue, that's another post. Thanks HiTechCoach, and Xenou.

 

[HiTechCoach]

You're welcome. Glad Xenou and I could assist.

 

[Bob Ully]

You're welcome. Glad Xenou and I could assist.

I'm not totally out of the woods yet. I get the error "Command Text was not set for Command Object". This is my code. I'm adding in 1 record, at the end of the datafile. This occurs at the Rst.Open line. I have programmed, but generally speaking, I was dealing with logical problems, and I didn't have to get involved with objects and properties, and I'm getting tripped up because this is my first excursion into this area. Thans.
Sub Macro1()
Dim CnnConn As ADODB.Connection
Dim Rst As ADODB.Recordset

Dim DBFullName As String
Dim Cs1 As String

'Select Worksheet
Worksheets("Macros").Select
F1 = Range("C3").Value
F2 = Range("C5").Value

DBFullName = F2

Cs1 = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="

Set CnnConn = New ADODB.Connection
With CnnConn
.connectionString = Cs1
.Open DBFullName
End With

'Setup Record
Set Rst = New ADODB.Recordset
Rst.ActiveConnection = CnnConn
Rst.CursorType = adOpenKeyset
Rst.LockType = adLockOptimistic
Rst.CursorLocation = adUseServer
Rst.Source = sSql

Rst.Open
Rst.MoveLast

Worksheets("Data").Select

'Record Here
Rst.AddNew
Rst.Fields("Acct Number") = ActiveCell.Offset(1, 1).Value
Rst.Fields("Data Source") = ActiveCell.Offset(2, 1).Value
Rst.Fields("Entity") = ActiveCell.Offset(3, 1).Value
Rst.Fields("GL Number") = ActiveCell.Offset(4, 1).Value
Rst.Fields("Loan Group") = ActiveCell.Offset(5, 1).Value
Rst.Fields("GL Group") = ActiveCell.Offset(6, 1).Value
Rst.Fields("Orig Date") = ActiveCell.Offset(7, 1).Value
Rst.Fields("Orig Trem") = ActiveCell.Offset(8, 1).Value
Rst.Fields("RemWam") = ActiveCell.Offset(9, 1).Value
Rst.Fields("Org Amt") = ActiveCell.Offset(10, 1).Value
Rst.Fields("Pr Bal") = ActiveCell.Offset(11, 1).Value
Rst.Fields("Intr Rate") = ActiveCell.Offset(12, 1).Value
Rst.Fields("Maturity Date") = ActiveCell.Offset(13, 1).Value
Rst.Fields("Balloon Date") = ActiveCell.Offset(14, 1).Value
Rst.Fields("DueDate") = ActiveCell.Offset(15, 1).Value
Rst.Fields("RateFlag") = ActiveCell.Offset(16, 1).Value
Rst.Fields("AMICategory") = ActiveCell.Offset(17, 1).Value
Rst.Fields("CurrBalxRate") = ActiveCell.Offset(18, 1).Value
Rst.Fields("CurrBalxOrigWam") = ActiveCell.Offset(19, 1).Value
Rst.Fields("CurrBalxRWam") = ActiveCell.Offset(20, 1).Value
Rst.Update

Rst.Close
CnnConn.Close

End Sub

 

[xenou]

There's a couple of errors in your code:

The connection string is incomplete
You don't have any value given for sSQL, which in any case sounds like it should be the name of the table you are inserting records into, not a SQL string per se.

Try to follow this example closely. I've only used one field to test this, to spare me the typing, but once it works with one field it should work with the rest. I should advise not to use spaces in field names if you have a choice - good database table names and fields names should start with a letter followed by only letters or numbers. This makes life easier in the long run.

It is also dangerous to use active sheet and active cell references, so be careful. That makes your code dependent on the user selecting the right sheets and cells when the code runs, which sooner or later will lead to a mistake.

[COLOR="Navy"]Sub[/COLOR] Macro1()
[COLOR="Navy"]Dim[/COLOR] CnnConn [COLOR="Navy"]As[/COLOR] ADODB.Connection
[COLOR="Navy"]Dim[/COLOR] Rst [COLOR="Navy"]As[/COLOR] ADODB.Recordset
[COLOR="Navy"]Dim[/COLOR] DBFullName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Cs1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]


    DBFullName = "C:\myTemp\Database1.accdb"
    
    Cs1 = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & DBFullName & ";" & _
        "Persist Security Info=False;"
    
    [COLOR="Navy"]Set[/COLOR] CnnConn = [COLOR="Navy"]New[/COLOR] ADODB.Connection
    CnnConn.Open Cs1
    
    [COLOR="Navy"]Set[/COLOR] Rst = [COLOR="Navy"]New[/COLOR] ADODB.Recordset
    Rst.Open "Table1", CnnConn, adOpenKeyset, adLockOptimistic, adCmdTable
        
    Rst.AddNew
    Rst.Fields("Acct Number") = Format(Int(Rnd() * 10000), "000000") [COLOR="SeaGreen"]'//a random value[/COLOR]
    Rst.Fields("Last Modified") = Now [COLOR="SeaGreen"]'//to timestamp my test runs[/COLOR]
    Rst.Update
    
    Rst.Close
    CnnConn.Close

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Bob Ully]

There's a couple of errors in your code:

Will try later this afternoon. Thanks. Typically, I setup the Macro to choose the datasheets, and data, the user doesn't choose anything. There are only a small number of users for this application. Thanks.

Bob U.