0

I'm using two Access databases (front end and back end).

My code for query work, but I get it to work with updating the database. What am I doing wrong?

I get a runtime error 3078 for the DoCmd.RunSQL strSql on line 25.

Set cnn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & CurrentProject.Path & "\DB_Cryk.accdb"

cnn.Open strConnection

MemberID = txtMemberID.Value

strSql = "UPDATE Cryk " & _
"SET Membership = '" & txtMembership.Value & "', " & _
"    Memberstatus = '" & txtMemberstatus.Value & "', " & _
"    Membername = '" & txtMembername.Value & "', " & _
"    Memberaddress = '" & txtMemberaddress.Value & "', " & _
"    Memberzip = '" & txtMemberzip.Value & "', " & _
"    Membercity = '" & txtMembercity.Value & "', " & _
"    Memberphone = '" & txtMemberphone.Value & "', " & _
"    Membermail = '" & txtMembermail.Value & "', " & _
"    Memberyear = '" & txtMemberyear.Value & "', " & _
"    Dateofbirth = '" & txtDateofbirth.Value & "', " & _
"    Memberno = '" & txtMemberno.Value & "', " & _
"    Memberfee = '" & txtMemberfee.Value & "', " & _
"    Memberpayment = '" & txtMemberpayment.Value & "'" & _
"WHERE MemberID= '" & MemberID & "'"

DoCmd.RunSQL strSql

cnn.Close
Set cnn = Nothing
braX
  • 11,506
  • 5
  • 20
  • 33
Kim K
  • 3
  • 2
  • 1
    Possible duplicate of [What's wrong with this SQL code? (VBA MS Access)](https://stackoverflow.com/questions/51856631/whats-wrong-with-this-sql-code-vba-ms-access) – Gustav Feb 23 '19 at 17:53
  • You assume that all fields are of data type Text, which they probably are not. Also, consider using _parameters_ to avoid the issue completely. – Gustav Feb 23 '19 at 17:55
  • I would print out the resulting `strSql` string to see if it is valid SQL. I echo Gustav's sentiment, use parameters. String concatenation of SQL queries opens you up to SQL injection. Here's an example of using parameters in VBA: https://stackoverflow.com/a/50102644/2675670 – Richard Feb 23 '19 at 17:58
  • Thanks, I have very little experience with sql the last 15 years, so can you help with an example based on my code using parameters? – Kim K Feb 24 '19 at 08:08

1 Answers1

0

Error 3078 indicates that the target table does not exist in your database.

Note that, although you open an ADO connection to the database DB_Cryk.accdb, you execute your SQL statement using the DoCmd.RunSQL method, which operates on the current database.

Instead, if you want the SQL to be executed in your DB_Cryk.accdb database, you should use the Execute method of the ADODB Connection object, e.g.:

cnn.Execute strsql

Where query parameterization is concerned, you may wish to refer to this superb answer, specifically, the 'Using ADO' section.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80