Solved

ODBC Refresh Links when Access Login Form Opened

Posted on 2012-03-18
45
536 Views
Last Modified: 2012-04-27
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
0
Comment
Question by:pskeens
  • 25
  • 20
45 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37735307
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37735324
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37735327
Also note that this fragment must be a valid server instance:

Server=tcp:s06.winhost.com\mydatabasename
0
 
LVL 2

Author Comment

by:pskeens
ID: 37735475
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37735685
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.
0
 
LVL 2

Author Comment

by:pskeens
ID: 37735876
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37736309
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;"
0
 
LVL 2

Author Comment

by:pskeens
ID: 37736450
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)
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37737223
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37745721
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37745784
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37746599
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37746785
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37750589
No Dice Bill,  Here is the error.  I'm confused because i'm not using Windows Authentication, I'm using sQL Authentication

Error
0
 
LVL 2

Author Comment

by:pskeens
ID: 37750637
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37750949
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37755743
Bill Exact same error.  It links the tables fine but when I go to open a table the authentication error happens.  Very strange.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37756772
HI,

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

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

Bill
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37756807
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37760620
still getting an error (See attachment), a somewhat longer error.  Is this a lost cause?
Capture.PNG
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37760695
Hi,

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

Regards,

Bill
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37760712
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:pskeens
ID: 37761480
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37761506
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37761633
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37761714
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37761716
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37761717
Again - What is the recordsource of the form?
0
 
LVL 2

Author Comment

by:pskeens
ID: 37761808
The Login form is unbound with two unbound controls and a button to login.
0
 
LVL 2

Author Comment

by:pskeens
ID: 37761840
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37762105
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37762649
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37762729
Can you link the tables and then open them without error without using the form?  

Another thought - what version of MS Access?
0
 
LVL 2

Author Comment

by:pskeens
ID: 37763183
Let me try your suggestion and then I will post back on linking before opening the form.  

I am using MS Access 2010
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37763829
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
0
 
LVL 2

Author Comment

by:pskeens
ID: 37765510
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?
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37765607
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...
0
 
LVL 2

Author Comment

by:pskeens
ID: 37765723
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???
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37765738
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37765767
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.
0
 
LVL 2

Author Comment

by:pskeens
ID: 37766372
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
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37766733
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?
0
 
LVL 2

Author Comment

by:pskeens
ID: 37850692
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.
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 37850921
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
0
 
LVL 2

Author Closing Comment

by:pskeens
ID: 37903439
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now