Solved DSN-Less Connection String - Troubling (1 Viewer)

[Ashfaque]

Hi,
I got following DSN-Less code at Microsoft website. Since I m new to SQL Server, I have few queries.

1.

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
    Next
If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

At the end it was said if I need to use the above code, after copying into a new module, I also have to use few code that is below which I have to place OnOpen event of my startup form

Private Sub Form_Open(Cancel As Integer)
If AttachDSNLessTable("authors", "authors", "(Local)", "Pubs", "", "") Then
'// All is okay.
Else
'// Not okay.
End If
End Sub

I need to ask 2 questions.
1. what value I should supply in place 2 times author, (Local) mentioned in above. I think in place of Pubs I have to provide my db name on Server.
2. In module I have replaced bold line with following
stConnect = "ODBC;DRIVER=SQL Server;SERVER=***.***.*.*;DATABASE=********;UID=**;PWD=**********"

What else do I need to add or replace so the code could run.

 

[arnelgp]

the first 2 are:
1. the link table name (to create) in your db.
2 the SQL table name in SQL server.

 

[Ashfaque]

the first 2 are:
1. the link table name (to create) in your db.
2 the SQL table name in SQL server.

You mean first authors will be T_Nationality(this tbl is on server) and for second authors..will be same T_Nationality (bcs I need same name)? Could you please elaborate? Also let me know if the bold line in main code I replaced correctly..

 

[Ashfaque]

the first 2 are:
1. the link table name (to create) in your db.
2 the SQL table name in SQL server.

Yes connected. Thanks....The only thing is only one table connected bcz in below code.

Private Sub Form_Open(Cancel As Integer)
If AttachDSNLessTable("T_Nationality", "T_Nationality", "(Local)", "HRINAT", "", "") Then
'// All is okay.
Else
'// Not okay.
End If
End Sub

Need to find way to either auto check other tables in server and connect OR I will put all tbl names in a local table and with loop I should get them connected 1-by-1.

I will try first n let you know....

Thanks,

 

[arnelgp]

i think your best option is the Later.

 

[Ashfaque]

i think your best option is the Later.

Thanks Arnel for your timely support.

I created a local tble entered all the tbls and thru a loop get them all connected once the db start up form is open. Now trying to disconnect all tbls if the user attempts to enter wrong pwd at startup form or at closing the db. Anyways this is issue at least has been solved.