syntax error 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' ". I am using ms access 2007 and It runs fine when I run it in sql studio.

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)

 

[PHV]

What about this ?
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"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[abenitez77]

That helped, I also had to put sys.objects in brackets ie [sys].[objects]. It creates the passthru query but now it turns my passthru query into this below which will not run. I now get this error msg: ODBC -- call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'. (#102)

SELECT O.name AS TableName, C.Name AS ColumnName, C.Max_Length AS ColumnSize, T.Name AS ColumnType, C.precision AS prec1, C.scale AS scale1
FROM ((sys) objects AS O INNER JOIN (sys) columns AS C ON O.Object_ID = C.Object_ID) INNER JOIN (sys) types AS T ON C.System_Type_ID = T.System_Type_ID
WHERE O.type = 'U' AND T.schema_id = '4' AND O.name = 'CLAIMS'
ORDER BY 1, 2, 3, 4;

Here is my updated code:

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] O "
sql = sql & "INNER JOIN [sys].[columns] C ON O.Object_ID = C.Object_ID) "
sql = sql & "INNER JOIN [sys].[types] T ON C.System_Type_ID = T.System_Type_ID "
sql = sql & "WHERE O.type = 'U' AND T.schema_id = 4 "
sql = sql & "ORDER BY 1, 2, 3, 4"


I also get "Type mismatch" error when i use Debug.Print qODBC
When i put the brackets in the passthru query that is created, it runs fine.

 

[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]"