Saving data to DB2 table trough Access 2000?

Question

I have this insert query

INSERT INTO MY_DB_MY_LINKED_TABLE (FIELD1, FIELD2, FIELD3, FIELD4, TIMETOENTER) values ('Sometext', '2018', '1234567', '20/11/17', 3)

Field1 - Text,

Field2 - Text,

Field3 - Text,

Field4 - Date/Time,

TIMETOENTER - Number

Not that I am using the db scheme name as well in the query.

And my job is to make this VB.NET (with Visual Studio 2005) application work on Windows 10. As you can see I am using a linked table and it contains only "_"(underscores) without dots.

My driver is

provider=microsoft.jet.oledb.4.0;data source='C:\Documents and Settings\MyUserName\My Documents\MyApp\bin\MyApp.mdb';

I have this utility function which uses ADODB.Connection.Execute() statement

Public Sub executeSqlStatement(ByRef dbConn As ADODB.Connection, ByVal sqlStmt As String, ByVal debugMode As Boolean, ByRef statusBar As System.Windows.Forms.ToolStripStatusLabel)

    Try
        dbConn.Execute(sqlStmt, CommandType.Text)
    Catch ex As Exception
        Dim funcName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name
        Utils.PrintToConsole("sql stmt: " & sqlStmt)
        Utils.PrintToStatusBar(funcName & ": " & ex.Message, debugMode, statusBar)
    End Try
End Sub

I have tried to execute the query as it is in SQL View in Access 2000 query. It works. I see the row in the table.

The problem is when I run the command above executeSqlStatement() even on Windows XP, it crashes with error:

Syntax error in INSERT INTO statement.

Not sure what the problem is?

The query works executed in Access 2000. But I am not able to send direct sql command to DB2 since it has old 32-bit driver, while newer versions of Access can process 64-bit commands. Yes we installed Access 2000 but still not sure why I get the error? Any ideas?

Yes I know it should be reworked with ADO.NET but no time for that.

Provide the field types, as mapped in Access, for all fields (not the db2 field types). — Erik A, Mar 30 '18 at 13:46
Your insert statement looks good , dont understand why u are recieving this erro ?? — Christopher H., Mar 30 '18 at 13:59
@zackraiyan Hmm, could the Date/Time be causing problems with string. But it works, in other cases, not sure... — Vlad, Mar 30 '18 at 14:03
Ow..it has a DateTime column ? Just change it's data type to varchar/short text in the database and all will be good...No issues :) — Christopher H., Mar 30 '18 at 14:04
try removing the datetime column from the insert statement and lemme know the results — Christopher H., Mar 30 '18 at 14:10
@zackraiyan yes seems simple but I am not allowed to do that. It must be Date/Time. Still it works in other cases, not sure why this error... — Vlad, Mar 30 '18 at 14:10
So, it has nothing to do with the dte time then , let me check again :) — Christopher H., Mar 30 '18 at 14:13
are u sure you have underscore in the table name or spaces ? — Christopher H., Mar 30 '18 at 14:20
@Vlad, This is likely an issue with passing the string ('20/11/17') to the DateTime field. It may work in Access since it may be applying your regional settings to the conversion, but the Jet provider will not do that. The best advice is to use a ADODB.Command object with a parameterized query. — TnTinMn, Mar 30 '18 at 15:22

Saving data to DB2 table trough Access 2000?

0 Answers0