Solved Linking Tbls Remotely - Sometime Works Sometime Not

[Ashfaque]

Hi,

I got this DSNless code lines somewhere from net to link SQL Server tables into my MS Access FE Db remotely. Since we dont have static /web host server, our IT keep changes IP address every now and then. Then to solve this issue I decided to place a text box on login form where remote user will type IP address first and it will save in local tbl and then below code will take that IP address and execute further.

This is successful. But it work sometime and it doesnt. Then user calls me from remote and then I have to connect his pc and then place IP address in below code line instead of using varibale MyNewIP and then it works.

Can someone tell me why this happens. I mean it connect sometime and sometime not. My remote user know the each time changed IP address because parallaly another db is running on his system and from there he know the new IP address.
So when sometimes it runs successfully no issue but when doest work even when the IP address is correct and stops at below line.

CurrentDb.TableDefs.Append td

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & " ;DATABASE=mydb;UID=hr;PWD=mypwd"

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

Dim MyNewIP As String
MyNewIP = Nz(DLookup("NewIP", "T_IP"), 0)

If Len(stUsername) <> 0 Then

 stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & " ;DATABASE= mydb;Trusted_Connection=No"
    Else
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & " ;DATABASE=mydb;UID=hr;PWD=mypwd"

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

AttachDSNLessTable_Err:

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

End Function

 

[bastanu]

Have you tried treating the IP address as a string:
stConnect = "ODBC;DRIVER=SQL Server;SERVER='" & MyNewIP & "' ;DATABASE=mydb;UID=hr;PWD=mypwd"
Might be worth a try.
Cheers,

 

[arnelgp]

my connection is like this:

stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & MyNewIP & "\SQLExpress;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=sample;"

you Only specify the IP and forgot the SQL Server name.

 

[Ashfaque]

Have you tried treating the IP address as a string:
stConnect = "ODBC;DRIVER=SQL Server;SERVER='" & MyNewIP & "' ;DATABASE=mydb;UID=hr;PWD=mypwd"
Might be worth a try.
Cheers,

Yes I treated my IP address saved in tbl T_IP is text type field. And tried the above way I explained but working properly.

 

[Ashfaque]

my connection is like this:

stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & MyNewIP & "\SQLExpress;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=sample;"

you Only specify the IP and forgot the SQL Server name.

Thanks Arnel,

In your code where you want me to place USERID and PWD to connect the server tbls in MS Access FE that is at remote.

 

[arnelgp]

you use your Code, but specify the ServerName:

If Len(stUsername) <> 0 Then

 stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & "\TheServerNameHere ;DATABASE= mydb;Trusted_Connection=No"
    Else
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & "\TheServerNameHere ;DATABASE=mydb;UID=hr;PWD=mypwd"

    End If

 

[Ashfaque]

Have you tried treating the IP address as a string:
stConnect = "ODBC;DRIVER=SQL Server;SERVER='" & MyNewIP & "' ;DATABASE=mydb;UID=hr;PWD=mypwd"
Might be worth a try.
Cheers,

Thanks Bastanu,

But considring string'" & MyNewIP & "' is not working instead it is displaying Server login dialog where in Server combo show '198.52.45.120' mean the IP address is quoted with apostrophe sign.

 

[Ashfaque]

you use your Code, but specify the ServerName:

If Len(stUsername) <> 0 Then

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & "\TheServerNameHere ;DATABASE= mydb;Trusted_Connection=No"
    Else
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & "\TheServerNameHere ;DATABASE=mydb;UID=hr;PWD=mypwd"

    End If

Thanks Arnel,

I will try this and let you know.

 

[Galaxiom]

The instance only needs to be specified for SQL Server Express. SQL Server doesn't require it.
(If there are multiple instance of SQL Server they are allocated different IP Addresses and Port numbers.)

There are no delimiters around the IP in the connection string.

The problem may be that the route to the new address has not updated when the connection is attempted.

 

[Ashfaque]

The instance only needs to be specified for SQL Server Express. SQL Server doesn't require it.
(If there are multiple instance of SQL Server they are allocated different IP Addresses and Port numbers.)

There are no delimiters around the IP in the connection string.

The problem may be that the route to the new address has not updated when the connection is attempted.

Thanks Galaxiom,

You are correct. My below line working properly.

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & MyNewIP & ";DATABASE=Mydbname;UID=MyUserIDHere;PWD=MyPwdHere"

What I was making mistake is I was connecting from same network that is why it was not working. Therefore, I connected the remote pc from different Internet connection which is our of our local connection and the db run smoothly by supplying new IP address.

Thanks to Arnel, Bastanu as well for their support.

Regards,