VBA Excel-Access 3251 Runtime error

Question

I currently use Microsoft Office 2007 and have linked an Access 2007 Database to Excel 2007 Database. So far so good. I can update the Access Database and it shows automatically in the Excel file. Here is where I have the problem.

When I try to update an Access Database from within Excel; I keep getting the runtime 3251 Error. =>runtime error now resolved but NEW issue regarding a NULL value when im trying to add data?

Sub ADODBExcelToAccess()
'Collecting data from the
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

    '---NOTE: Sheet is set to auto refresh data from the database on loading---

    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
            "Data Source=" & Application.ActiveWorkbook.Path & "\linktest.accdb;"

    ' open a recordset (i.e. a table)
    Set rs = New ADODB.Recordset

    rs.Open "linktest", cn, adOpenKeyset, adLockOptimistic, adCmdTable

    ' all records in a table
    For i = 4 To 16
        x = 0

        Do While Len(Range("K" & i).Offset(0, x).Formula) > 0
            With rs
                'create a new record
                .AddNew
                .Fields("ID") = Range("A1" & i).Value
                .Fields("PriceID") = Range("B1").Value
                .Fields("ProductCode") = Range("C1").Value
                .Fields("Price") = Range("D1" & i).Value
                .Fields("CurrencyType") = Range("E1").Value
                .Fields("Type") = Range("F1").Value
                .Fields("Production") = Range("G1" & i).Value
                .Fields("Quantity") = Range("H1" & i).Value
                .Fields("Details") = Range("I1" & i).Value
                .Fields("DateUpdated") = Range("J1" & i).Value
                .Fields("Setup") = Range("K1" & i).Value

                ' stores the new record
                .Update

            End With
            x = x + 1
        Loop
    Next i
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

The current reference library that I'm using is Active X Data Objects 2.1 Library.

Answer 1

ANSWER

OK so the issue was I was getting a runtime error when trying to "Add" a new record to a database with

Do While Len(Range("K" & i).Offset(0, x).Formula) > 0
        With rs
            'create a new record
            .AddNew
            .Fields("ID") = Range("A1" & i).Value ...

This was so I could save the records from an Excel Spreadsheet into an Access Database (both office 2007).

I opened the connections option in Excel slected the properties of my connection and changed the mode to the following ";Mode=Share Deny None!"

So now my connection string looks like this:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\linktest.accdb;Mode=Share Deny None;Extended Properties="";
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;
Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:
New Database Password="";Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

I then had a second issue when i returned an error stating that the data I was trying to return gave a "NULL" value. This was because I was trying to save data t the primary key in the database which returned the error.

I simply commented out this line and this resolved this issue.

I also changed the reference to Microsoft ActiveX Data Objects 6.1 Library

Answer 2

Thought I'd show a DAO alternative - it's the default db method now with the newer Access versions. As I noted in the code it requires a reference to Microsoft Office 14.0 Access database engine Object Library or whatever version you have on your system now.

Option Explicit

Sub DAOExcelToAccess()
    ' Requires Reference To
    ' Microsoft Office 14.0 Access database engine Object Library

    Dim db      As DAO.Database
    Dim rs      As DAO.Recordset
    Dim strFile As String
    Dim i       As Integer
    Dim x       As Integer

    strFile = Application.ActiveWorkbook.Path & "\linktest.accdb"
    Set db = DBEngine.OpenDatabase(strFile)
    Set rs = db.OpenRecordset("linktest", dbOpenDynaset)
    With rs
        For i = 4 To 16
            x = 0

            ' repeat until first empty cell in column A
            Range("A2").Activate

            'create a new record
            .AddNew
            ' Remove ID if it is AutoIncrement field
            ' .Fields("ID") = Range("A1" & i).Value
            .Fields("Area") = "Test" & i
            .Fields("ProductCode") = Range("C1").Value
            .Fields("Price") = Range("D1" & i).Value
            .Fields("CurrencyType") = Range("E1").Value
            .Fields("Type") = Range("F1").Value
            .Fields("Production") = Range("G1" & i).Value
            .Fields("Quantity") = Range("H1" & i).Value
            .Fields("Details") = Range("I1" & i).Value
            .Fields("DateUpdated") = Range("J1" & i).Value
            .Fields("Setup") = Range("K1" & i).Value

            ' stores the new record
            .Update

             x = x + 1
        Next i

        .Close
    End With
End Sub