Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 18th, 2005, 10:47 AM
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
  #2 (permalink)  
Old July 18th, 2005, 11:00 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

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!).
Reply With Quote
  #3 (permalink)  
Old July 18th, 2005, 11:05 AM
Registered User
 
Join Date: May 2005
Location: Philadelphia, PA, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've done that. My code is now:

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

with etc...


The problem still persists.




Reply With Quote
  #4 (permalink)  
Old July 18th, 2005, 02:05 PM
Registered User
 
Join Date: May 2005
Location: Philadelphia, PA, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
OPEN A FILE DIALOG IN MS ACCESS 2000 DevineRule BOOK: Beginning ASP 3.0 3 January 28th, 2010 07:59 AM
connect jdbc/odbc bridge to ms- sql server 2000 sushant2002 SQL Server 2000 0 July 3rd, 2007 08:28 PM
looking for access 2000 to sql server 2000 sql/que method SQL Server 2000 0 July 7th, 2005 12:46 PM
Access and update linked SQL Server 2000 table Lizu Access 9 May 10th, 2004 12:42 PM
ODBC driver 2000.81.7713.00 for SQL 7.0 Jai SQL Server 2000 1 October 13th, 2003 11:56 PM



All times are GMT -4. The time now is 09:31 AM.


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