ODBC and MS Access DSN-less connection VB

Teradata Employee

ODBC and MS Access DSN-less connection VB

I have succesfully set up an Access front-end that links tables from a Teradata database.  Everything works fine when I have the tables linked through a DSN.  I am trying to set up a DSN-less connection, but I am encountering the following error:  "Reserved Error (-7778): there is no message for this error"

My code is below.  The code below uses a method that deletes the table definition and then appends a new one.  I have also tried using the TableDef RefreshLink method and am getting the same error.  I also understand that the ODBC connection string for Teradata should start with "Provider=Teradata", but when I use Provider I get prompted with the DSN selection box, so I have been using "Driver=Teradata".

Option Compare Database

'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to a database without using a DSN
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword 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

stConnect = "ODBC;Driver=Teradata;DBCName=" & stServer & ";Database=" & stDatabase & ";Uid=" & stUsername & ";Pwd=" & stPassword

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


AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
Teradata Employee

Re: ODBC and MS Access DSN-less connection VB

Sorry, should have mentioned that I'm in Access 2010 and using the TD version of the ODBC driver