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

march.dian

New member
Local time
Today, 10:51
Joined
Nov 6, 2017
Messages
4
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

Registered User.
Local time
Today, 17:51
Joined
Nov 2, 2015
Messages
823
Do you have a table or query in the rate.mdb file called A?
Check that the connection's .status returns 1.
 

march.dian

New member
Local time
Today, 10:51
Joined
Nov 6, 2017
Messages
4
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:51
Joined
May 7, 2009
Messages
19,169
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

Immoderate Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
26,999
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.

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.
 

march.dian

New member
Local time
Today, 10:51
Joined
Nov 6, 2017
Messages
4
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

MVP / VIP
Local time
Today, 17:51
Joined
Jan 14, 2017
Messages
18,186
Jet is only used for A2003 or earlier
ACE became the standard for A2007 onwards
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
26,999
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.

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

Adelina_RO

Member
Local time
Today, 19:51
Joined
Apr 9, 2021
Messages
42
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
 

Users who are viewing this thread

Top Bottom