ODBC Refresh Links when Access Login Form Opened

HELP hopefully quickly.  I need to refresh my ODBC Links when the login form opens.  The users table is contained in these ODBC Links so it has to work when they open the form.  The form opens when the database opens, on successful login the form is open but hidden.


I have this code:
    Dim DataBaseName As String
    Dim UID As String
    Dim PWD As String
    
    DataBaseName = "mydatabasename"
    UID = "myusername"
    PWD = "mypassword"
    
     strConnectionString = "ODBC;Driver={SQL Server Native Client 10.0};" & _
    "Server=tcp:s06.winhost.com\mydatabasename;Database=" & DataBaseName & ";" & _
    "User=" & UID & ";" & _
    "Password=" & PWD & ";" & _
    "Option=3;"

Open in new window


However this is not working.  I have this code in the "On Open" event of the form
LVL 2
pskeensAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill RossProgrammerCommented:
Hi,

What error message are you getting?

In your connection string you are passing UID and Pwd.  Where are you getting the values for these variables?

Thanks,

Bill
Bill RossProgrammerCommented:
Try this string hard coded.

strConnectionString = "ODBC;Driver={SQL Server Native Client 10.0};" & _
    "Server=tcp:s06.winhost.com\mydatabasename;Database=mydatebasenome" & ";" & _
    "User=myusername;Password=mypassword;"

Replace the my... with the actual values.
Bill RossProgrammerCommented:
Also note that this fragment must be a valid server instance:

Server=tcp:s06.winhost.com\mydatabasename
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

pskeensAuthor Commented:
Thanks I will try this. Yes the ServerNames, Username, and password I have removed and put a generic name in the string for this questions purpose.

I am getting the Username and Password by setting the variables in the DIM statements
Bill RossProgrammerCommented:
You code should work but the "Server=" parameter looks suspicious.  This must be a valid instance.  You can determine what it should be by using the wizard and ODBC to link to a table.  Once you've connected through the wizard go to the table design and click properties and examine the ODBC string.
pskeensAuthor Commented:
Not working.  Looking at Winhost they state an OLEDB string as this:

"Provider=sqloledb;Data Source=[SQLServerName];Initial Catalog=[DatabaseName];User Id=[SQL Login];Password=[SQL Password]"  

When I use this string the ODBC Connection window opens and asks me to select the data source.
Bill RossProgrammerCommented:
I'm confused.  Does the oledb provider string work?

You can also try the basic SQL driver...

strConnectionString = "ODBC;Driver={SQL Server};" & _
    "Server="SQLServerinstancename";Database=mydatebasenome;" & _
    "User=myusername;Password=mypassword;"
pskeensAuthor Commented:
Bill I never got either string to work.  I'm confused to, but that easy to do.   The OLEDB string opens the connections window to select the connection, it does not automatically refresh the links.   This is the String that WinHost says to use (actually the only one I have found)
Bill RossProgrammerCommented:
OK.  Here is what we need to do to determine the connection requirements.

Start Control Panel, Administrative Tools, Data Sources (ODBC) and create a new User DSN.  

Click Add and select SQL Server - not Native Client.

Click Finish then Name it whatever - say TEST.

Enter the Server - you will not be able to browse for it.  You will need to test portions of the supplied name.  Try the tcp:s06.winhost.com\mydatabasename first.

Click Next

Use SQL Auth... with user name and Password they supplied

Click Next

Check change default database to your databasename

Click Next, finish and Test Data Source.

Once you get an OK take a screen shot of the Test screen and answer OK.

This is now a DSN we can use to link the tables but more importantly we can use it to create our DSN less connection.

Let me know how it goes.

Bill
pskeensAuthor Commented:
Ok Bill, sorry took so long.  

I have completed the test odbc connection.  Attached are the two screenshots with a succeeding connection

Testing setup  Succeeding test
Bill RossProgrammerCommented:
Hi,

OK.  Link any table using this connection.  
Then select design of the table.  
Then go to properties of the table design view.
Note that you'll have the correct driver, server, database and table name format.

Put that in your connection string:

strConnectionString = "ODBC;Driver={SQL Server Native Client 10.0};" & _
    "Server=tcp:s06.winhost.com\mydatabasename;Database=mydatebasenome" & ";" & _
    "User=myusername;Password=mypassword;"

Regards,

Bill
pskeensAuthor Commented:
This is what shows in properties, which doesnt look right

ODBC;DSN=TEST;Description=Testing for String;APP=Microsoft Office 2010;DATABASE=DB_30065_pulsetrack;TABLE=dbo.TBL_BOL_DET
Bill RossProgrammerCommented:
Hi,

It's correct.  We have all the parameters needed.  Here is your "DSNLess" connection string.

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=tcp:s06.winhost.com;" & _
"Database=DB_30065_pulsetrack;" & _
"User=myusername;" & _
"Password=mypassword;"

Let me know if that works.

Bill
pskeensAuthor Commented:
No Dice Bill,  Here is the error.  I'm confused because i'm not using Windows Authentication, I'm using sQL Authentication

Error
pskeensAuthor Commented:
Okay, update:  This string actually linked the tables successfully, BUT now when trying to access the data or open any table I get the error attached

RefreshODBCLinks "ODBC;Driver=SQLServer};Server=s06.winhost.com;" & _ Description=DB_30065_pulsetrack;Uid=DB_30065_**********_****;Pwd=***********"
Capture.PNG
Bill RossProgrammerCommented:
Hi,

Try this:

RefreshODBCLinks "ODBC;Driver={SQLServer};Server=s06.winhost.com;" & _ Description=DB_30065_pulsetrack;Uid=DB_30065_**********_****;Pwd=***********;" & _ "Trusted_Connection=NO"

Note be sure to insert the { in front of SQL...

regards,

Bill
pskeensAuthor Commented:
Bill Exact same error.  It links the tables fine but when I go to open a table the authentication error happens.  Very strange.
Bill RossProgrammerCommented:
HI,

That's surprising.  Add the following parameters and see what happens.

"ODBC;Driver={SQLerver};....   ;Network=dbmssocn;Persist Security Info=True"

Bill
Bill RossProgrammerCommented:
New string:

RefreshODBCLinks "ODBC;Driver={SQLServer};Server=s06.winhost.com;" & _ Description=DB_30065_pulsetrack;Uid=DB_30065_**********_****;Pwd=***********;" & _ "Trusted_Connection=NO;Network=dbmssocn;Persist Security Info=True"

Bill
pskeensAuthor Commented:
still getting an error (See attachment), a somewhat longer error.  Is this a lost cause?
Capture.PNG
Bill RossProgrammerCommented:
Hi,

No.  I do this all the time to SQL.  Post the complete code for the RefreshODBCLinks routine w/o passwords.

Regards,

Bill
Bill RossProgrammerCommented:
Hi,

Make a slight change...

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=tcp:s06.winhost.com;" & _
"Database=DB_30065_pulsetrack;" & _
"UID=yourusername;" & _
"PWD=yourpassword;" & _
"Persist Security Info=True"

Bill
pskeensAuthor Commented:
That did not work either Bill.  Here is the Code in the module.  Module name is ODBCRefresh

Public Sub RefreshODBCLinks(newConnectionString As String)
    Dim db As dao.Database
    Dim tb As dao.TableDef
    Set db = CurrentDb
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing
End Sub

Open in new window


This is the code in the "On Open" even of the login form:

    Dim DataBaseName As String
    Dim UID As String
    Dim PWD As String
    Dim strConnectionString As String
    
'***** This link works
RefreshODBCLinks "ODBC;Driver={SQL Server};Server=tcp:s06.winhost.com;Database=DB_30065_pulsetrack;" & _
    "Uid=DB_30065_pulsetrack_user;Pwd=bethany1011;Persist Security Info=True"

Open in new window


The Variables was in there because I was using variables in the beginning with the string and never removed them yet.
Bill RossProgrammerCommented:
Hi,

Now I'm confused.  Are you passing the UID and PWD to the form's on open event via variables or is the string you are passing hard coded?
Is the Login form bound to a linked table?

Try to link your tables before the form is opened like this:

Create a new procedure:

Public OpenLoginForm()
  'run link code
 ....
  Docmd.OpenForm Login

End Procedure


Regards,

Bill
pskeensAuthor Commented:
Bill, The user id and password is hardcoded for the connection string.  The way it works is the Login form opens when the application opens.  The Login form "ON OPEN" event runs the RefreshODBC string.  The SQL login stays the same.  The Login form looks up the Users Username and Password from a linked table TBL_USERS.  This is why I want to relink the tables when the form/application opens so it can access the user table.

Hope that helps to clarify it.
Bill RossProgrammerCommented:
Hi,

Let's try linking the tables then opening the form.

If the tables are linked before the form opens it could be easier to troubleshoot.

What is the recordsource of the form?

Regards,

Bill
Bill RossProgrammerCommented:
BTW - you can run any code at startup - it does not have to be a form.  Use a macro to do your startup tasks sequentially.  Once it works then remove the call for the startup form and name the macro AutoExec and it will run at startup.

Bill
Bill RossProgrammerCommented:
Again - What is the recordsource of the form?
pskeensAuthor Commented:
The Login form is unbound with two unbound controls and a button to login.
pskeensAuthor Commented:
Here is the Login Form Code:

__________________________________________________________________________

   If IsNull(Me.txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Must Enter Username", vbInformation, "PULSE ERROR"
        Me.txtUsername.SetFocus
        Exit Sub
    ElseIf IsNull(Me.txtPassWd) Or Me.txtPassWd = "" Then
        MsgBox "Must Enter Valid Password", vbCritical, "PULSE ERROR"
        Me.txtPassWd.SetFocus
        Exit Sub
    End If
   
               
        Dim strSql As String
        Dim rs As Recordset
       
        strSql = "Select * from TBL_USER WHERE [USER_LOGIN] = " & Chr(34) & Me.txtUsername & Chr(34) & ""
        Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
       
        '--- Check to see if username exists or is entered properly
       
            If rs.RecordCount > 0 Then
           
                rs.MoveFirst
                   
                    '--- Check to ensure correct password
                   
                    If Me.txtPassWd <> rs("USER_PASSWORD") Then
                        Me.lblPassInc.Visible = True
                        Me.txtPassWd = ""
                        rs.Close
                        Set rs = Nothing
                        Exit Sub
                    End If
                   
                    '--- Populate Full Name on Login for variable to use in application headers
                   
                    Me.txtFirstName = rs("USER_FIRST_NAME") & " " & rs("USER_LAST_NAME")
                   
                    lngMyEmpID = Me.txtUsername '--- Set Varialbe username for application use
                    Me.txtPassWd = ""           '--- Clear Password Field
                    Me.txtCurUser = lngMyEmpID
                   
                rs.Close
                Set rs = Nothing
               
                    '--- Open Control Panel (frm_Home)
                   
                    DoCmd.OpenForm "frm_HOME", acNormal
            Else
           
               
                Me.lblUserInc.Visible = True
                Me.txtUsername.SetFocus
               
                rs.Close
                Set rs = Nothing
                Exit Sub
               
            End If
Bill RossProgrammerCommented:
I didn't check the code closely.  Did you try to link the tables then open the form?

If the code works Ok to link the tables can you open a table and select data?

Bill
pskeensAuthor Commented:
The Code works to link the tables.  I have tested this using a local host linked table then opening the form and seeing the tables now linked to the host in this string.  

The issue is that you cannot open a linked table or retrieve the data because this error comes up (authentication error) once you try.
Bill RossProgrammerCommented:
Can you link the tables and then open them without error without using the form?  

Another thought - what version of MS Access?
pskeensAuthor Commented:
Let me try your suggestion and then I will post back on linking before opening the form.  

I am using MS Access 2010
Bill RossProgrammerCommented:
Hi,

One thing to check is to make sure you're using a trusted location for your DB.  File, Access Options, Trust Center, Trusted Locations.

Sorry this is taking so long.  It should be fairly easy to get this working so I must be missing something somewhere.

Bill
pskeensAuthor Commented:
Ok, I decided to try the DNS route just to see if we can get this thing to work.  DNS method works

RefreshODBCLinks "ODBC;DSN=TEST_Prod;Description=TEST_Production;Trusted_Connection=No;APP=Microsoft Office 2010;" & _
    "DATABASE=DB_DATABASE_TEST;Uid=DB_DATABASE_TEST_user;Pwd=TESTPASSWORD;"

Now why cant we get DNS-less to work?
Bill RossProgrammerCommented:
If this is the DNS version the then DSNLess version should be:

"ODBC;Driver={SQL Server};Server=NAMEOFSERVERUSEDFORTEST;" & _
"Trusted_Connection=No;" & _
 "DATABASE=DB_DATABASE_TEST;Uid=DB_DATABASE_TEST_user;Pwd=TESTPASSWORD;"

The above assumes you selected the SQL Server driver (not the Native Client) when you created the DSN named TEST_PRODUCTION.

If you used Native client then it would depend on the version for the driver.  It would typically be: "Driver={SQL Server Native Client 10.0}"

Seems like we've tried this though...
pskeensAuthor Commented:
Bill, That string does not have a server associated with it.  I put the server in the string then all bets are off I get the error as before "Connection not from a trusted source and cannot be used with windows".  

absolutely bizzare.  I may have to leave it as a DSN connection???
Bill RossProgrammerCommented:
You're correct.  I changed the string above - early here....  

Not sure what the issue is.  Again I would try connecting before the form opens and if that works then open the form.  At least you can test the connection separate from the form.
Bill RossProgrammerCommented:
Also - modify the procedure :

 RefreshODBCLinks(newConnectionString As String)
...
            Debug.Print "Refreshed ODBC table " & tb.Name
to

Debug.Print "Refreshed ODBC table " & tb.Name & "-" & tb.connect

Review the debug window and see if the string is really being set.
pskeensAuthor Commented:
I have called the function from AutoExec macro on DB Open.  Again the tables refresh but cannot access any data or open the tables

Here is the function I am calling on open and it returns the same trusted error

Function RefreshODBCLinks()


'Public Sub RefreshODBCLinks(newConnectionString As String)
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim newConnectionString As String
    Set db = CurrentDb
   
    newConnectionString = "ODBC;Driver={SQL Server};Trusted_Connection=No;Server=tcp:s06.winhost.com;APP=Microsoft Office 2010;" & _
    "DATABASE=DB_TEST_Database;Uid=DB_TEST_Database_user;Pwd=TestPassword;"
   
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name & "-" & tb.Connect

            'Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing
   
End Function
Bill RossProgrammerCommented:
What does the connection in the debug window show?

Also go to one of the linked tables in design and view the properties.  See what the connection looks like.

Are you using a trusted location?
pskeensAuthor Commented:
Sorry for the long delay Bill.  I have been working on another project but now back on this one.

Im not sure what you are referring to in the debug window.  I am not using a trusted location in the connection string.  

For now I have went to the DNS route for refreshing the links because it works.  I would like to go full DNS-less but for the life of me cannot figure it out why it will not work.   Thanks for the help and patience.
Bill RossProgrammerCommented:
No problem.  I cannot understand why DSNLess would not work as it's the same as DSN but on the fly.  

Debug.Print "Refreshed ODBC table " & tb.Name & "-" & tb.Connect

The statement above will type everything after the  Debug.Print statement into the immediate window.  Open any module then open your form and note the immediate window results.  This will help us figure out what's happening.

Bill

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pskeensAuthor Commented:
Bill, still not resolved but marking as accepted because you were so diligent in helping me!  I am using DNS Refresh until I move hosting.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.