Can someone take a look at this code to relink ODBC table

reena

Registered User.
Local time
Today, 22:45
Joined
Jul 18, 2001
Messages
17
Hi,

Basically I am trying to link a SQl Table through code without having to go to tools and then using add linked table manager and then refresh.The above steps I am trying to code.
I have this function in a module and use the command button of a form to call the function. Though I hard coded the DSN to the database belonging the SQL Sever, still prompts me for a user to select the DSN, how could I avoid it. I am breaking my heads. Please any advice.
The DSN already exists and is created through Settings then control panel and then Data Sources(ODBC).
I have no clues as to what is going on.

Thanks in advance

This is my code

Public Function Linktabledao()

Dim strlinkname As String
Dim strdbname As String
Dim strtablename As String
Dim strdsnname As String
'links or relinks a single table
'returns true or false based on err value

strlinkname = "satya"
strdsnname = "Freslib Production"
strtablename = "satya"
strdbname = "Freslib"

Dim db As Database
Dim tdf As TableDef

On Error Resume Next
Set db = CurrentDb
'if the link already exists, delete it
Set tdf = db.TableDefs(strlinkname)
If Err.Number = 0 Then
db.TableDefs.Delete strlinkname
db.TableDefs.Refresh
Else
'ignore error and reset
Err.Number = 0
End If
'create a new tablede object
Set tdf = db.CreateTableDef("satya")
tdf.Connect = "ODBC;Database = " & strdbname & ";DSN = " & strdsnname & ";Integrated Securtiy = True"
tdf.SourceTableName = strtablename

'append to the database's tabledefs collection

db.TableDefs.Append tdf
Linktabledao = (Err = 0)

End Function
 
Since you point out that it "...still prompts me for a user...", I would say that the SQL Server is requiring a Login. WinNT authentication is not being used and this is why you are getting prompted for a user.

To make things short, you will need to provided the "user" and "pwd" parameters to the SQL server login. These can be added to the tdf.Connect line in your code.
 

Users who are viewing this thread

Back
Top Bottom