syntax error for querydef

[abenitez77]

I get an error on the last line that says "Syntax error (missing operator) in query expression 'O.Object_ID = C.Object_ID INNER JOIN sys.types As T ON C.System_Type_ID = T.System_Type_I' "
Dim db As DAO.Database
Dim qODBC As DAO.QueryDef
Dim sqODBC As String, sConnectDAO As String
Set db = CurrentDb
Set Conn = Application.CurrentProject.Connection
sqODBC = "qry_PassThru_" & strSQLTableName & "_REF"
sql = "SELECT O.name as TableName, C.Name as ColumnName, C.Max_Length as ColumnSize, T.Name as ColumnType, C.precision, C.scale "
sql = sql & "FROM sys.objects AS O "
sql = sql & "INNER JOIN sys.columns AS C ON O.Object_ID = C.Object_ID "
sql = sql & "INNER JOIN sys.types As T ON C.System_Type_ID = T.System_Type_ID "
sql = sql & "WHERE O.type = 'U' AND T.schema_id = '4' AND O.name = '" & strSQLTableName & "' "
sql = sql & "ORDER BY 1, 2, 3, 4"

'MsgBox sql

Set qODBC = db.CreateQueryDef(sqODBC, sql)

 

[John Big Booty]

Try phrasing it like;

Dim strSQL as String
strSQL = "SELECT O.name as TableName, C.Name as ColumnName, C.Max_Length as ColumnSize, T.Name as ColumnType, C.precision, C.scale " [B][COLOR="Red"]& _[/COLOR][/B]
      "FROM sys.objects AS O " [B][COLOR="Red"]& _[/COLOR][/B]
      "INNER JOIN sys.columns AS C ON O.Object_ID = C.Object_ID " [B][COLOR="Red"]& _[/COLOR][/B]
      "INNER JOIN sys.types As T ON C.System_Type_ID = T.System_Type_ID " [COLOR="Red"][B]& _[/B][/COLOR]
      "WHERE O.type = 'U' AND T.schema_id = '4' AND O.name = '" & strSQLTableName & "' " [COLOR="Red"][B]& _[/B][/COLOR]
      "ORDER BY 1, 2, 3, 4"

Also SQL is a reserved word and could well be causing you trouble.

 

[abenitez77]

tried that and same error msg.

 

[John Big Booty]

The last line should look like;

"ORDER BY 1, 2, 3, 4[B][COLOR="Red"];[/COLOR][/B]"

 

[abenitez77]

SQL server does not require a semicolon at the end of the statement. this is a passthru query. the error msg is complaining about a missing operator. I tried your suggestion and same error msg.

 

[abenitez77]

I ended up changing my query to this below...I don't think ms access likes [tablename].[fieldname].

sql = "SELECT O.[Name] as TableName, C.[Name] as ColName, T.[Name] as ColType, C.[Length], " & _
"C.[prec], C.[scale], C.[xtype], C.[colorder] "
sqlFrom = "FROM (SysObjects as O " & _
"INNER JOIN SysColumns as C ON O.[Id] = C.[Id]) " & _
"INNER JOIN SysTypes as T ON T.[xtype] = C.[xtype] "
sqlWhere = "WHERE O.[type] = 'U' AND T.[Name] <> 'sysname' " & _
"AND O.[Name] = '" & strSQLTableName & "' " & _
"ORDER BY O.[Name]"

 

[LPurvis]

That query certainly looks familar.

We'll assume that this is indeed a passthrough (it wouldn't parse in Jet anyway and the name impies as much - though, obviously, there would need to be code subsequent to that which you're showing).

Consequently it's irrelevant what "Access" likes or not.
That you've changed from the System Views to the older System tables implies that perhaps you are hitting an older version of SQL Server? (Seems unlikely but possible I suppose).

Perhaps you could show your full code too.

 

[abenitez77]

I am using sql 2007 and ms access 2007. This is the full code for those interested:

Sub ListTablesSQL()
On Error GoTo Err_ListTablesSQL
Dim temp As String, temp2 As String
Dim sql As String, sqlFrom As String, sqlJoin As String, sqlWhere As String, sqlOrderBy As String
Dim rst As Recordset
Dim rst_SQLTable As Recordset
Dim rst_tblFields As Recordset
Dim db As DAO.Database

Dim strSQLServer As String
Dim strSQLDatabase As String
Dim strSQLTableName As String
Dim strLinkedTableName As String

' Get variables for PassThru Process
Dim qODBC As DAO.QueryDef
Dim sqODBC As String, sConnectDAO As String, sVariable As String

'Set d = CurrentDb
Set db = CurrentDb
Set Conn = Application.CurrentProject.Connection
Set rst = db.OpenRecordset("tblLinkTables", dbOpenDynaset)

If rst.EOF = True And rst.BOF = True Then
Exit Sub
Else
rst.MoveFirst
temp = "0"
Do Until rst.EOF = True
'Loop thru the records in tblLinkTables to get the values to connect to SQL
strSQLServer = rst![SQL_Server]
strSQLDatabase = rst![SQL_Database]
strSQLTableName = rst![SQL_TableName]
strLinkedTableName = rst![LinkedTableName]

'make sure we have records in our table.
If temp <> rst![recno] Then
Select Case rst![LinkType]
Case "LinkedTable"
' Do nothing... Properties already captured in other function.
Case "PassThru"
On Error Resume Next
sqODBC = "qry_PassThru_" & strSQLTableName & "_REF"
'Delete existing pass thru query.
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, sqODBC
DoCmd.SetWarnings False

sql = "SELECT O.[Name] as TableName, C.[Name] as ColName, T.[Name] as ColType, C.[Length], " & _
"C.[prec], C.[scale], C.[xtype], C.[colorder] "
sqlFrom = "FROM (SysObjects as O " & _
"INNER JOIN SysColumns as C ON O.[Id] = C.[Id]) " & _
"INNER JOIN SysTypes as T ON T.[xtype] = C.[xtype] "
sqlWhere = "WHERE O.[type] = 'U' AND T.[Name] <> 'sysname' " & _
"AND O.[Name] = '" & strSQLTableName & "' " & _
"ORDER BY O.[Name]"

Set qODBC = db.CreateQueryDef(sqODBC, sql & sqlFrom & sqlWhere) '& sqlFrom & sqlJoin & sqlWhere & sqlOrderBy)
Debug.Print qODBC

' use the values from the table tblLinkTables to create the string connection below.
sConnectDAO = "ODBC;Description=SQLPassThru;DRIVER=SQL Server;SERVER=" & strSQLServer & ";DATABASE=" & strSQLDatabase & ";Trusted_Connection=Yes;"
qODBC.Connect = sConnectDAO
zODBC.sql sql & sqlFrom & sqlWhere
qODBC.Close

'DoCmd.OpenQuery "qry_SQLTables"
' Move thru the Passthru table that has the table definitions.
Set rst_SQLTable = db.OpenRecordset(sqODBC, dbOpenDynaset)
Set rst_tblFields = db.OpenRecordset("tblFields", dbOpenDynaset)

If rst_SQLTable.EOF = True And rst_SQLTable.BOF = True Then
Exit Sub
Else
rst_SQLTable.MoveFirst
temp2 = "0"
Do Until rst_SQLTable.EOF = True
rst_tblFields.AddNew
rst_tblFields!TableLoc = "SQLSERVER"
rst_tblFields!TableName = rst_SQLTable!TableName
rst_tblFields!FieldName = rst_SQLTable!ColName
rst_tblFields!FieldType = rst_SQLTable!ColType
rst_tblFields!FieldSize = rst_SQLTable!Length
rst_tblFields!Precision = rst_SQLTable!Prec
rst_tblFields!Scale = rst_SQLTable!Scale
rst_tblFields!SQLType = rst_SQLTable!xType
rst_tblFields!ColOrder = rst_SQLTable!ColOrder
rst_tblFields.Update
rst_SQLTable.MoveNext
Loop
rst_SQLTable.Close
rst_tblFields.Close
End If
End Select
End If
' Go to next record
temp = rst![recno]
rst.MoveNext
Loop
rst.Close
MsgBox ("Finished Updating SQL data dictionary.")
DoCmd.Close acForm, "frmLinkTables"
End If

Exit_ListTablesSQL:
Exit Sub
Err_ListTablesSQL:
MsgBox Err.Description
Resume Exit_ListTablesSQL

End Sub

 

[LPurvis]

SQL 2008 perhaps?

What I did actually mean was the code from the original post that wasn't working.
Although, I imagine that it was all the same, except that the SQL has now changed (back to the system tables as opposed to views - well, views behaving as tables to views really)?

My point is that there should be no need for that.
If the SQL Server version supports them, then those views are the preferred choice (and do work - in a PT or otherwise).

Cheers.

 

[boblarson]

SQL server does not require a semicolon at the end of the statement.

And actually Access doesn't either. When I'm building SQL strings in VBA I don't ever include the semi-colon at the end.