Switching Connection Type

[markphsd]

When I used linked tables with a dsnless connection to SQL server i can use a few reserved words by putting [] around them.

However, if i connect directly to the SQL Server database using a sql server connection string with ado, it doesn't work. Dos this just mean i'll have to change the database field names?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[PHV]

With ado I think you have to use the native sql engine dialect (T-SQL for your case).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[markphsd]

Actually, no it doesn't for this case, but it's something to look into.

What i'm talking about is if i write something like the following, i get no errors, but it doesn't work properly.

If i use currentproject.connection, then it's fine.

dim rst as adodb.recordset
dim sSQL as string

sSQL = "Select FirstName, LastName, [Position] " & _
"FROM tblEmployees WHERE EmployeeID =" & 1

rst.Open sSQL, GetSQLCon, adOpenForwardOnly, adLockPessimistic

sEmpName =rst!FirstName & " " & rst!LastName ' this is okay

sPosition = rst!Position ' this is where the error occurs.. the rst!position always fails, but it fails silently, not in vba. it returns nothing, not even null, and vba doesn't make a noise about it.

rst.close ' this is okay also
set rst = nothing


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[PHV]

And this ?
sPosition = rst("Position")
or this ?
sPosition = rst("[Position]")
or this ?
sPosition = rst![Position]
or this ?
sPosition = rst.Fields(2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[markphsd]

I haven't tried rst.fields(2), but then I'd go ahead and use getrows.

I tried the other three, the aforementioned results.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[cmmrfrds]

Since "position" is an ANSI 92 and Jet 4.0 reserved word it is hard to say where it is going to cause a problem. Best to avoid if possible.

You could try putting an alias on the name in the select list.

Select
lastname,
position as "myposition"
From table

Maybe add an ADO error handler which is a good idea anyway and see if ADO gives you the error.


Public Function TheFunction()
On Error GoTo Errhandler
Dim cn as new adodb.connection

"your ado code here"

Exit Function
Errhandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function

Or
iterate through the connection properties for currentproject.connection and see if Microsoft has a setting you can put on your connection