Run-time error ‘-2147217900(80040e14)’ : Invalid SQL statement; expected ‘Delete’

[march.dian]

Hi All,

Can anyone help me with these error warning on my macro code:
Run-time error ‘-2147217900(80040e14)’ :
Invalid SQL statement; expected ‘Delete’, ‘Insert’, ‘Procedure’, ‘Select’, or ‘update’


The code are:

Public Sub calculate()

Const Src = "DATA.mdb"
Const Dest = "OutputC.mdb"
Const Rate = "rate.mdb"
Const Provider = "Microsoft.Jet.OLEDB.4.0"
Const ValDate = "10/31/2017" 'update this at every valuation period
Const FixDate = "10/31/2017" 'fixed
Const InitDate = "12/31/2008" 'First date of valuation

Dim cn1 As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim cn3 As New ADODB.Connection
Dim cn4 As New ADODB.Connection
Dim cn5 As New ADODB.Connection
Dim cn6 As New ADODB.Connection
Dim cn7 As New ADODB.Connection
Dim cn8 As New ADODB.Connection
Dim cn9 As New ADODB.Connection
Dim cn10 As New ADODB.Connection
Dim cn11 As New ADODB.Connection
Dim cn12 As New ADODB.Connection
Dim cn13 As New ADODB.Connection
Dim cn14 As New ADODB.Connection

Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset
Dim rs5 As New ADODB.Recordset
Dim rs6 As New ADODB.Recordset
Dim rs7 As New ADODB.Recordset
Dim rs8 As New ADODB.Recordset
Dim rs9 As New ADODB.Recordset
Dim rs10 As New ADODB.Recordset
Dim rs11 As New ADODB.Recordset
Dim rs12 As New ADODB.Recordset
Dim rs13 As New ADODB.Recordset
Dim rs14 As New ADODB.Recordset

Dim field_name(34) As String
Dim i As Long
Dim AgeToSearch As Integer
Dim RemTenureToSearch As Integer
Dim tenure As Integer
Dim saldo As Double
Dim tenure_awal As Integer
Dim newexpdate As Date
Dim saldo_akhir As Double
Dim initial_loan As Double
Dim unearned_period As Double
Dim total_period As Double

cn1.Open "Provider=" + Provider + ";" + "Data Source=" + Src
Set rs1.ActiveConnection = cn1
rs1.Source = "MYSIS"
rs1.Open

cn2.Open "Provider=" + Provider + ";" + "Data Source=" + Dest
Set rs2.ActiveConnection = cn2
rs2.Source = "OutputC"
rs2.LockType = adLockOptimistic
rs2.Open

cn3.Open "Provider=" + Provider + ";" + "Data Source=" + Rate
Set rs3.ActiveConnection = cn3
rs3.Source = "A" '0-6string
rs3.Open

Warning:
Runtime Error ‘-2147217900(80040e14)’ :
Invalid SQL statement; expected ‘Delete’, ‘Insert’, ‘Procedure’, ‘Select’, or ‘update’

I found this warning after I run the macro above,
please kindly help me, after i debug the macro code ,the yellow highlighted on the code "rs3.Open"

Your help will be highly appreciated!
Thank you in advance

 

[static]

Do you have a table or query in the rate.mdb file called A?
Check that the connection's .status returns 1.

 

[march.dian]

Do you have a table or query in the rate.mdb file called A?
Check that the connection's .status returns 1.

Thank you for your reply.
Yes, My macro code is connected to the table access rate.mdb called A which the access tables is linked to excel file (refresh by linked table manager).
Can you inform me how to check the status connection to the mdb file?
Thanks

 

[arnelgp]

you are opening a recordsource but does not specify which table to open on all your rs.Open statement, eg:



rsX.Open "SELECT * FROM table;"

 

[The_Doc_Man]

Actually, Arnel, he's got a reference to the recordset's .Source property just above the Open and the two prior .Open actions worked.

I want to focus on these lines of code.

...
Const Src = "DATA.mdb"
Const Dest = "OutputC.mdb"
Const Rate = "rate.mdb"
...

cn1.Open "Provider=" + Provider + ";" + "Data Source=" + Src
...

cn2.Open "Provider=" + Provider + ";" + "Data Source=" + Dest
...


cn3.Open "Provider=" + Provider + ";" + "Data Source=" + Rate
...

From your discussion, you get past the first two cases. So syntactically, that third case looks to be OK. Which means it must be a semantics error and THAT means that there is something wrong with the rate.mdb reference. Or with the referenced database. Verify your file permissions, for example.

Verify, perhaps by single stepping through the code, that in fact the first two recordsets ARE open and that the .Open operation returned no errors. HINT: Open the locals window and see whether the rs1, rs2, and rs3 objects are instantiated as recordsets. Open recordsets expand differently than closed ones when using the Locals windows.

If rs1 and rs2 are good, then you have no other place to look than rate.mdb because it is the first one to fail. If rs1 and rs2 don't actually open, then you have a syntax issue.

 

[arnelgp]

yes he did.
maybe he need to open excel directly, as what this site suggest:
https://technet.microsoft.com/en-us/library/ee692882.aspx

 

[march.dian]

Thanks for your reply all,

I resolved the issue on my end by changing the connection string replacing Jet Engine to ACE engine. I works on windows 7 , the issue appears after update the windows.

 

[isladogs]

Jet is only used for A2003 or earlier
ACE became the standard for A2007 onwards

 

[The_Doc_Man]

By the way, just picking nits, ...

I would use ampersands (string concatenation operator) rather than plus signs in this context - but since all variables are strings, VBA would end up doing the right thing anyway. Like I said, it is a nit-pick.

cn1.Open "Provider=" [COLOR="Red"]&[/COLOR] Provider [COLOR="red"]&[/COLOR] ";" [COLOR="red"]&[/COLOR] "Data Source=" [COLOR="red"]&[/COLOR] Src

 

[Adelina_RO]

For me it was a driver issue... I installed Mysql connector 8.0.26 and i had this issue, but then i downgraded to 8.0.25 and things worked normally. The thing is that if i used a command instead of recordset, the update would work normally, so it was an obvious driver issue