I have the following code in a Virtual Basic module being used in an Access form to get data from a linked table with an ODBC connection.
Criteria = "[CDATE] >= " & parmFromDate & " And [CDATE] <= " & parmToDate
Sel_stmt = "Select * From SYSADM.DB_A02 Where " & Criteria
Set ODBC_Workspace = CreateWorkspace("ODBC_Workspace01", "CQ11", "", dbUseODBC)
Set DB = DBEngine.Workspaces(0).OpenDatabase(CurrentDb().Na me)
Set ODBC_Connection = ODBC_Workspace.OpenConnection("ODBC_Connection01", _
dbDriverPrompt, True)
ODBC_Connection.QueryTimeout = 0
'Debug.Print ODBC_Connection.Connect
'Set RS_In = ODBC_Connection.OpenRecordset("SYSADM.DB_A02", dbOpenForwardOnly)
Set RS_In = ODBC_Connection.OpenRecordset(Sel_stmt, dbOpenForwardOnly)
Set RS_Out = DB.OpenRecordset("tblStatisticalCounts", DB_OPEN_DYNASET)
When I execute the form, I get an ODBC call failed. While debugging, I discovered that the statement in red is erroring out with a bad syntax error and the words "Cursor for " have been added to what was moved to Sel_stmt. I am trying to learn this stuff but I don't have a lot of resources at my office to which to turn. Can somebody out there please help me?