Access VBA: How to write code to test ODBC username/password?

[VAer]

make Sybase login effort (i.e. pass the values to Sybase) - step 2 in your earlier post 19. .

This is my question in this thread. Post #19 is my idea, but not my code. My question is about how to write the code.

[Minty]

To be honest opening a connection , is opening a connection, the overhead for trying to link to a table would probably be tiny.
You could possibly use the DBEngine.RegisterDatabase command . Syntax here https://msdn.microsoft.com/en-us/lib...or=-2147217396

If you used trapped any errors you could use that to return a true or false boolean. Something like

Function TestLink(stUserName as String , stPassword as String) As Boolean

    Dim stConnect as String
    
    On Error Goto TestLinkErr

    stConnect = "The start of Yourconnection string ;"  & "UID = " & stUserName  & "; PASSWORD = "   &  stPassword 

    Debug.Print stConnect

     DBEngine.RegisterDatabase "YourDatabase", "YourODBC Connector ", True, stConnect  ' Look up the correct syntax from the link!

     TestLink = True
     Exit Function
TestLinkErr:

     TestLink = False
     MSGBox "Error Connecting : "  & Err.Description    ' Comment this out after testing
End Function

Check the syntax carefully in the link to get it right for your Sybase connection.

[VAer]

To be honest opening a connection , is opening a connection, the overhead for trying to link to a table would probably be tiny.
You could possibly use the DBEngine.RegisterDatabase command . Syntax here https://msdn.microsoft.com/en-us/lib...or=-2147217396

If you used trapped any errors you could use that to return a true or false boolean. Something like

Function TestLink(stUserName as String , stPassword as String) As Boolean

    Dim stConnect as String
    
    On Error Goto TestLinkErr

    stConnect = "The start of Yourconnection string ;"  & "UID = " & stUserName  & "; PASSWORD = "   &  stPassword 

    Debug.Print stConnect

     DBEngine.RegisterDatabase "YourDatabase", "YourODBC Connector ", True, stConnect  ' Look up the correct syntax from the link!

     TestLink = True
     Exit Function
TestLinkErr:

     TestLink = False
     MSGBox "Error Connecting : "  & Err.Description    ' Comment this out after testing
End Function

Check the syntax carefully in the link to get it right for your Sybase connection.

Thank you. I will try it when I am in Office, no Sybase in personal computer. Today is holiday.

[VAer]

Regarding saving Username/Password as GLOBAL variables. Where are the Global variable values stored? In the Access file or personal computer?

If the file is shared in workplace shared drive, two people (Mike & Jennifer) use the same file at the same time, from their own computers. Let us say, Mike opens the file first from his computer, then Mike's Username/Password saved to Global variable. Later on Jennifer opens the same file from her computer, then Jennifer's Username/Password saved to Global variable. In this case, the file is open in both computers, if Mike runs any programs from form Interface, will Access use Mike's Username/Password or use Jennifer's Username/Password?

Thanks

[VAer]

Username = FormLoginTextboxUsername.Value
Password = FormLoginTextboxPassword.Value

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=" & Username & ";PWD=" & Password & ";LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "HelloWorld", False, True

See the attached screenshot(at the bottom of screenshot, there is linked table HelloWorld), I use above code acLink to successfully link a Sybase table. However, the above code should only be run once(during application development phase), it should not use the same code acLink when user opens the Access file. So the above code should not be in final delivery product.

To avoid confusion, I will explain my issue as clearly as I can.

1) When I build Access Application, I use my own Username/Password to link the Sybase table into Access Application. So when the product is ready for delivery, the linked table is inside Access file.
2)However, every time when opening the Access application, Access asks for Login ID/Password to re-activate linked table HelloWorld. In this case, whoever opens the Access application, the application will use his/her Username/Password to re-activate the linked table. How to write code to re-activate already-linked table HelloWorld? Maybe I should not use the term already-linked, I mean the table already showing in Access left sidebar. If I manually click the table, a window pop up for Login ID/Password, that means when the file is closed, the Sybase table connection is deactivated.
3) The reason I cannot use the above code: if application run the above code, it will link the same Sybase table to Access and the table name will appear as HelloWorld2 . I do not want to remove HelloWorld either, then run acLink code to re-link the table, since some Access queries are built from already-linked table HelloWorld, if I remove the already-linked table HelloWorld, all queries built from HelloWorld will be lost. Even if running the code acLink will create a new table also called HelloWorld, but there are no queries built from the new table. The queries are built during Application development phase, and the queries are inside the Application when delivered as product.

[Minty]

Global Variables are stored in the local Access database file, and are destroyed when the application is closed down.

If you use a DSNless connection to the linked table (which is effectively what you are doing with your code above), you can destroy the connection either on closing the database, and/or on opening the database, and only recreate the link once a confirmed username and password are entered. The details would be contained within the users local database.

You can store them locally in a table or as global variables, the choice is yours.

Edit : All users should only ever use a local copy of the Front end database stored on their own machine.
Otherwise you will have lots of corruption and record locking problems.

[VAer]

Global Variables are stored in the local Access database file, and are destroyed when the application is closed down.

If you use a DSNless connection to the linked table (which is effectively what you are doing with your code above), you can destroy the connection either on closing the database, and/or on opening the database, and only recreate the link once a confirmed username and password are entered. The details would be contained within the users local database.

You can store them locally in a table or as global variables, the choice is yours.

Edit : All users should only ever use a local copy of the Front end database stored on their own machine.
Otherwise you will have lots of corruption and record locking problems.

Let us just assume that most colleagues prefer to keep only one copy of file in Workplace Share Drive(which is actually true for other Excel-based applications), and the file may be constantly updated (replaced with new version). In this case, refer to above post #34 Mike & Jennifer as example, will Access use Jennifer's Username/Password when Mike runs the program?

If most programs are only about running queries (not editing records), I don't think there will be locking problems. Moreover, I think the locking issue is determined by back end database table setting, when a record is opened for edit, someone else may open it as Read mode only, it does not matter if someone else opens the Access File from local copy or not.

[Minty]

No - you are incorrect about your assumptions and how Access works, especially multiple users accessing the same database at the same time.

It's not a question of preferring to have a single copy, it's a basic need for them to have a local copy.
I guarantee you will have issues trying to share a front end database on a network share between multiple users. A quick google will confirm my statement, regardless of the lack of editing.

It will also give you massive headaches with your user password model you want to use, as effectively someone will attempt to re-connect the linked table in the middle of someone else already using it.
That simply won't work. Trust me I'm a doctor....

[VAer]

No - you are incorrect about your assumptions and how Access works, especially multiple users accessing the same database at the same time.

It's not a question of preferring to have a single copy, it's a basic need for them to have a local copy.
I guarantee you will have issues trying to share a front end database on a network share between multiple users. A quick google will confirm my statement, regardless of the lack of editing.

It will also give you massive headaches with your user password model you want to use, as effectively someone will attempt to re-connect the linked table in the middle of someone else already using it.
That simply won't work. Trust me I'm a doctor....

Ok, doctor, I trust you. That is why I like excel xltm file, when user double click to open the xltm excel file, it opens a copy of Original file in the local machine, not opening the actual original copy in Shared drive folder. Let us say, the file is ABC.xltm, double click to open it as ABC1.xltm, double click and open second time as ABC2.xltm, etc.... Each opened file is independent in local machine temp folder, I guess. Access template file extension does not act like that.

Now how about questions in post #35? re-activate already-linked table

Thanks.

[Minty]

I'm signing off now, but someone will probably be able to assist with that.

You can probably get all the code you need from here though http://www.accessmvp.com/djsteele/DSNLessLinks.html which describes all sorts of ways of connecting and re-connecting remote tables. It's primarily looking at SQL or Access tables but you already know how to use the correct connection strings for your remote application.

Doug Steele's site is the goto resource for this type of material.

[VAer]

I'm signing off now, but someone will probably be able to assist with that.

You can probably get all the code you need from here though http://www.accessmvp.com/djsteele/DSNLessLinks.html which describes all sorts of ways of connecting and re-connecting remote tables. It's primarily looking at SQL or Access tables but you already know how to use the correct connection strings for your remote application.

Doug Steele's site is the goto resource for this type of material.

Okay, thanks.