p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Access 2000 "Open ODBC SQL Table for Additions" (http://p2p.wrox.com/showthread.php?t=31603)

cjdphlx July 18th, 2005 10:47 AM

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?



leehambly July 18th, 2005 11:00 AM

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!).

cjdphlx July 18th, 2005 11:05 AM

I've done that. My code is now:

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

with etc...


The problem still persists.





cjdphlx July 18th, 2005 02:05 PM

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?



All times are GMT -4. The time now is 07:40 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.