DNS less connections issues

[mcgrcoAgain]

hI. I want to connect msaccess to a sql server database using a dns less connection. I dont want each user to have to set up a system DNS as they all should have acceess to the sql server. The problem I have is that using the following code the data types are differnt between the sql server and the access database. doubles are not text in the database. I was to run this script on a large number of tables so I dont want to try to change the data types on tghe fly. Any suggestion on how this can be fixed or a better way of foing it. Many thanks

Code is below


Function AttachDSNLessTable(stLocalTableName As String, stServer As String, stDatabase As String, stRemoteTableName As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String


For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True

Exit Function

AttachDSNLessTable_Err:

If err.Number = 3011 Then

MsgBox stRemoteTableName & " does not exist for the date selected: ", vbCritical

Else
MsgBox "AttachDSNLessTable encountered an unexpected error: " & err.Description
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & err.Description
End If

AttachDSNLessTable = False
Set rs = Nothing
Set db = Nothing

End Function

 

[MikeC1408]

I have some code tucked away somewhere that is called by the AutoExec.

It looks at the system DNS of the client running the App and if the required one is missing it is automatically created for the user.

Would you like me to dig it out for you????

 

[mcgrcoAgain]

Hi MikeC1408 ,

After much trial and error It looks like the issue is realted to data type differnces between access and sql derver. In sql the decimal dattype trasnlates to text. I asked teh dba to change to float but hes firmly gaianst it as its ths the wrong dat type for financial data. I agree with him but I need to find away around it without have to convert the fields in the database.