Results 1 to 6 of 6
  1. #1
    mr.viskers is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4

    How to write data into linked MySQL table

    I have linked MySQL database into Access. In the "tables" view I can open and add data into the "book" table without problems.



    Now I need to make some code to write data. Below is the code I have used.
    Code opens the table fine, but when it comes to "dbBook.update" I get an error: "Run-Time error -2147217887 (80040e21): ODBC: call failed"

    Should I use different provider?


    Dim CurConn3 As New ADODB.Connection
    Dim CurDB3 As Database


    Dim dbBooks As New ADODB.Recordset


    Set CurDB3 = CurrentDb
    StrConn3 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\prg\forms.mdb;" & _
    "Jet OLEDB:System database=Y:\databases\secured.MDW"


    CurConn3.Open ConnectionString:=StrConn3, UserID:="user", Password:="1234"

    Set dbBooks = New ADODB.Recordset
    dbBooks.CursorType = adOpenDynamic
    dbBooks.LockType = adLockOptimistic

    dbBooks.Open "SELECT * FROM book", CurConn3, , , adCmdText

    dbBooks.AddNew


    dbBooks!Name = "testname"

    dbBooks.Update

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where is your link to MySql?
    Please show us a jpg of your working view of the linked MySql table.

  3. #3
    mr.viskers is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4
    Here is my connector:
    Click image for larger version. 

Name:	connector.jpg 
Views:	18 
Size:	43.6 KB 
ID:	19609

    and this is the linked MySQL table "book". If i double click it, it will open just fine. I can add and modify data
    Click image for larger version. 

Name:	linked_table.jpg 
Views:	18 
Size:	8.6 KB 
ID:	19610

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Do your Tables in SQL Server all have Primary Keys, or at least Unique Indexes, defined for them? Is the PK part of the RecordSource of the Form? If not they’ll be Read-Only using ODBC and won't allow adding Records.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    mr.viskers is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4
    There is one field which is typed as an Auto Increment and it is primary key.

  6. #6
    mr.viskers is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4
    So how can I add record to the table?
    How to check "is the PK part of the recordsource of the form".

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 10-20-2014, 04:00 PM
  2. Inserting Data to Access Table (Local) from MySQL table (Net)
    By gambit1430 in forum Import/Export Data
    Replies: 2
    Last Post: 09-26-2014, 01:34 AM
  3. linked mysql database
    By nkuebelbeck in forum Misc
    Replies: 3
    Last Post: 05-30-2013, 09:58 AM
  4. Replies: 5
    Last Post: 05-16-2012, 12:48 AM
  5. MySQL Linked Tables - Carriage Return
    By warrenk in forum Access
    Replies: 1
    Last Post: 03-28-2010, 01:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums