I just recently started testing moving my Access tables to a SQL Server. I linked the tables using the menu bar (FileÒGet External DataÒLink Tables). This is fine, the table is now named “dbo_tblReportData” and I can edit it with a liked form. But, I have code within my Access database, which opens the data table and adds a new record:

     Set mydb = CurrentDb
     Set myTable = mydb.OpenRecordset("tblReportData", dbOpenTable)

     With myTable
                .AddNew
                ![Review Date] = txtReviewDate
                ![Analyst] = cboAnalyst
                ![Report Name] = cboReportName
                ![Report Date] = txtReportDate
                ![Review Time] = txtReviewTime
                ![Comments] = txtComments
                .Update
                .Close
            End With

This code works great with internal data tables but it does not work with the SQL Server linked table. How do I open a linked table when I need to add or edit a record?

The error number I get when I try to set myTable is 3219, and the error message I get is “Invalid operation”. I believe I get this error message because the linked table is not located in the CurrentDb.

Does anyone know how to add or edit a linked table using VBA Code?

As you mentioned... your table is now called "dbo_tblReportData" instead of "tblReportData"...

which means you will need to change your code to reflect this, or change your table name (entirely up to you which you change!).

I've done that. My code is now:

Set mydb = CurrentDb
Set myTable = mydb.OpenRecordset("dbo_tblReportData", dbOpenTable)

with etc...


The problem still persists.

I've managed to find out that the linked SQL Server table is read only. Anyone know how to open this kind of table as not read only?