VBA Excel - MySQL filter two fields error on Update - Run-time error '-2147467259 (80004005)'

dwsteyl

New Member
Joined
Sep 28, 2017
Messages
16
I have a MySQL database from which I request a recordset of clients, and then I filter the recordset for a specific client based on two fields: 1) client code and 2) year applicable. I then assign values from a userform's objects to the MySQL fields, and update that specific client's records to the database.

I get the following error upon updating the table (running 'rstIT.Update'), and I think it has to do with the way I am implementing the filter:

Run-time error '-2147467259 (80004005)': [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.2.36-MariaDB-log-cll-lve]Build WHERE -> insert_fields() failed
I am sure there must be an easier / more clever way to filter for the correct client, and update the records accordingly. Any help to resolve my current problem or another method would be greatly apppreciated!

VBA Code:
Dim cn As ADODB.Connection
Dim rstIT As ADODB.recordSet
Dim rstClients As ADODB.recordSet

Dim Jaar As String

Jaar = cboTaxPeriod.Text
ITID = lblClientCode.Caption

Application.DisplayAlerts = False

Dim str As String

str = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=myserveraddress;DATABASE=mydatabasename;PORT=myport;UID=user_"
str = str & LCase(LoggedInName)
str = str & ";PWD="
str = str & Password
str = str & ";FOUND_ROWS=1;"

Set cn = New ADODB.Connection
cn.ConnectionString = str
cn.Open
Set rstIT = New ADODB.recordSet

With rstIT
    .Open "IT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
End With

    rstIT.Filter = "ClientCode = '" & ITID & "' AND TaxYear = '" & Jaar & "'"

        If rstIT.EOF Then
            MsgBox "Client code does not exist in tax table.", vbOKOnly, "Choose new client code"
            GoTo GaanUit 'This closes everything and exits
        Else
            rstIT!TaxStatus.value = LTrim(RTrim(cboTaxStatus.Text))
            rstIT!TaxStatusStaff.value = RTrim(LTrim(lblTaxStatusStaff.Caption))
            
            If lblDueDate.Caption <> "" Then
                lblDueDate.Caption = Format(lblDueDate.Caption, "yyyy-mm-dd")
                rstIT!DueDate.value = lblDueDate.Caption
            Else
                rstIT!DueDate = Empty
            End If
                        
        End If

rstIT.Update
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,363
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Excel - MySQL filter two fields error on Update - Run-time error '-2147467259 (80004005)' - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

dwsteyl

New Member
Joined
Sep 28, 2017
Messages
16
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Excel - MySQL filter two fields error on Update - Run-time error '-2147467259 (80004005)' - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Hi Rory - thank you. Was not aware of the rule. Will follow in the future. Regards dwsteyl
 

Forum statistics

Threads
1,141,619
Messages
5,707,445
Members
421,508
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top