link a ODBC Table, the following Code still prompts for a DSN

[kavya]

Hi,
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&quot
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

 

[vbmanchris]

Since you DSN is two words, have you tried to place quotes around the DSN name? For example:

tdf.Connect = "ODBC;Database = " & strdbname & ";DSN =

'

" & strdsnname & "

'

;Integrated Securtiy = True"

Also, if you are using a DSN, you shouldn't need to include the database name, that info should already be encapsulated in the DSN.

Best of luck!

 

[TrojanRabbit]

I'll take a shot in the dark here. Probably won't help, though. Just what I can think of.

Did you copy & paste the code, or did you type it here? I noticed a mistype in Integrated Security. Don't know if that would cause it.

Have you run the debugger to make sure it's passing info that matches the DSN/DB correctly?

Does it ask for password as well? "UID=...;PWD=...".

I'm assuming the DSN is set up on all systems that use this.