Link to home
Start Free TrialLog in
Avatar of TrilobyteMKR
TrilobyteMKRFlag for United Kingdom of Great Britain and Northern Ireland

asked on

The Microsoft Jet database engine cannot find the input table or query 'MSysConf'.

Hi guys,
can you help me with this.
I'm trying to use a paramter SQL pass thru query from an access db using QueryDef.
To prove the pooint I'm using the Northwind DB supplied.

Private Sub Text0_AfterUpdate()
On Error GoTo ErrStuff
Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Dim qdef As QueryDef
Set qdef = db.CreateQueryDef("")
qdef.Connect = "ODBC;DRIVER=SQL Server;SERVER=CROWTHORNXXX;UID=SA;PWD=XXX;DATABASE=Northwind;"
qdef.SQL = "CustOrderHist '" & Me.Text0 & "'"

Set rs = qdef.OpenRecordset()

exitHere:
Exit Sub
ErrStuff:
Debug.Print Errors(0).Description
MsgBox Err.Number & " " & Err.Description
Resume exitHere

End Sub


if I run this code I keep getting
The Microsoft Jet database engine cannot find the input table or query 'MSysConf'.  Make sure it exists and that its name is spelled correctly.

Now I understand that this is a table that would exist in the SQL database. I haven't created it nor do i wish to. I just want to be able to use a simple pass thru query with a parameter using access front end and sql back end.

Surely this is a simple task. but I can't get it to work. I feel it's something to do with the connect string. I've copied the connection string from a DSN taht I created.

Can anyone help?

Regards

Malcolm
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image
Avatar of TrilobyteMKR
TrilobyteMKR
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Arthur,
are you saying I must create this MSysConf table in order to be able to use pass thry queries in access?
Malcolm
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TrilobyteMKR
TrilobyteMKR
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I don't think that it does as I almost have it working without the MSysConf file.
In my code I seem top have missed the bit about the local access query

My code has ended up as this

On Error GoTo ErrStuff
Dim db As Database

Set db = CurrentDb()
Dim Rs As Recordset
Dim qdef As QueryDef
Set qdef = db.QueryDefs("qry_custorderhist")
qdef.Connect = "ODBC;DRIVER=SQL Server;SERVER=(local);UID=SA;PW=xxx;DATABASE=Northwind;Trusted_Connection=Yes"
qdef.sql = "exec custorderhist '" & Me.Text0 & "'"
'DoCmd.OpenQuery "Qry_custorderhist"
qdef.Close
Set qdef = Nothing
Set db = Nothing
DoCmd.OpenForm "frm2", acFormDS
frm2.Forms.RecordSource = "qry_custorderhist"
exitHere:
Exit Sub
ErrStuff:
Debug.Print Errors(0).Description
MsgBox Err.Number & " " & Err.Description
qdef.Close
Set qdef = Nothing
Rs.Close
Set Rs = Nothing
Resume exitHere
'

End Sub