View Single Post
  #1 (permalink)  
Old July 18th, 2005, 10:47 AM
cjdphlx cjdphlx is offline
Registered User
 
Join Date: May 2005
Location: Philadelphia, PA, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access 2000 "Open ODBC SQL Table for Additions"

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?


Reply With Quote