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