Link to home
Start Free TrialLog in
Avatar of StampIT
StampITFlag for United States of America

asked on

Understanding ODBC bridge Authentication

In an MS Access database I have tables linked from SQL Server via an ODBC bridge. The bridge is set up with SQL Authentication. However when I link a table to this Access database I can use either the SQL user name and password or Trusted Connection. Either way the table is accessible. The connection strings are below for the same table. Is this normal ? If the bridge is set up with SQL Authentication I thought that is the way the SQL tables had to be linked. Trying to understand how this works so I can set up other clients. Thanks.

ODBC;DSN=ERP_Data;Description=ERP Database;Trusted_Connection=Yes;APP=Microsoft Office 2016;;TABLE=dbq.ds_WrkCtr

ODBC;DSN=ERP_Data;Description=ERP Database;UID=ERP_dbq;;APP=Microsoft Office 2016;;TABLE=dbq.ds_WrkCtr
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Not sure what you mean by "bridge" if you just mean a ODBC connection, OK.

With Access and a ODBC table, it will first look in the connection string of the linked table for info it needs.   Part of that can be a DSN.

 Aside from that, you can use SQL Authentication (a user name/password) or Trusted connections, or a combination of both.   Which you use depends on SQL's settings.   It may allow one or the other, or both.

Trusted connections is generally better I find, but there is no right or wrong here; it depends on the situation and what you have to work with.  For example if your working across multiple domains, then trusted connections are out unless there is a trust setup between them.

 What you should try and do with your app is avoid the use of DSN's.   Put everything needed in the connection string.  Also provide a means in the app for updating the server name and database it is pointing to.

Jim.
Avatar of StampIT

ASKER

Yes. I mean ODBC connection.

Our SQL Server is set up to accept SQL Server and Windows Authentication. There is just one domain.

I agree Trusted Connections are better.

Are you suggesting not using the Data Source Administrator and instead put everything needed into a connection string ? I am not sure how to do that or provide a way to update the server name and database.
<<Are you suggesting not using the Data Source Administrator and instead put everything needed into a connection string ?>>

 Yes.  Installing DSN's on an end users machine can be difficult and best if avoided altogether.

<< I am not sure how to do that or provide a way to update the server name and database.>>

  You would run through the tableDefs collection and modify the .Connect property (which is a string) for any ODBC table.

 See the following, which will help convert what you have and run through a lot of the details about what your asking:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

 also this has some code which you should find helpful (shows how to create a linked table from scratch in code):

https://support.microsoft.com/en-us/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in

 Again, it's just adding a tabledef object.

 Bounce back with any questions on the above.

Jim.
Another option would be to read my article on A more efficient Linked Table Manager, download and install the application.  This Access add-in will help you create DSN less connections to your SQL database, and can make it easy to update these (for moving from a development database to a production database).
Avatar of StampIT

ASKER

Jim,

Trying to replicate the "AttachDSNLessTable" function as a way to educate myself on how this works. So far I am getting an "invalid argument" error when I try to run the function from the Immediate window. Does the table need to exist in the database or does the function add it ?  New to this so not sure how it all goes together. Thanks.
Note: It is a common misunderstanding that you can provide authentication with the DSN definition in ODBC Administrator. You always have to add login info on connect (but Trusted Connection being the default).
Authentication in the ODBC Administrator is just used for retrieving and pre-occupying default database and further settings, and has no meaning beyond.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of StampIT

ASKER

Dale,

I would like to give this a try.

How do I know whether my Access version is 32 or 64 bit ? Assuming 64 but do not know for sure.  I am using Office 16.

Where is %AppData\Roaming\Microsoft\AddIns\ ?
It is %AppData%\Roaming\Microsoft\AddIns\ , and you can put that into e.g. Explorer to see where it points to.

On a 64bit OS you can (usually) just look for the binary location - is it in "Programs", then it is 64bit, in "Programs (x86)" it is 32bit (in most cases).
generally, most people have the 32 bit version of Office.  
User generated imageEvery version of Office seems to change this, in some versions you can get to this screen via "File => Help", in others it is buried in the File => Options => Resources => About.  I don't have 2016 installed anywhere, but in 2013, when you open an Access database you will see your name in the upper right corner with a drop down button. Click the dropdown and select account settings.

that path should read:

%appdata%\Roaming\Microsoft\addins\

because different versions of windows put that folder in different places.  Using  %appdata% causes Access (probably windows) to make the appropriate determination of the actual path to that folder.

Did you copy that from my article?
Avatar of StampIT

ASKER

Dale - Yes. Copied from your article.
Avatar of StampIT

ASKER

I was able to download and install the Add In. It appears to work. I was able to link a table from SQL Server using the Add In. What is the process to create a DSN-less link ? I have several Access databases that contain linked SQL tables. They are opened by the user via Access runtime. Thanks.
Trying to replicate the "AttachDSNLessTable" function as a way to educate myself on how this works. So far I am getting an "invalid argument"

 This functions creates a brand new table.   make sure when you copy/paste that code, you are scrolling of to the right.  The full declare is:

Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean

  Username and password are optional, but server, database (in SQL) at a minimum must be supplied.

  Dale's got a great utility BTW, but you still might want to play around with the code in the articles I posted so you understand how it works.

Jim.
Avatar of StampIT

ASKER

Jim - I agree. I believe the issue with the function is in the "Set td =" statement. It doesn't like dbAttachSavePWD. I do not see where that is defined anywhere. Any ideas ? Thanks.
dbAttachSavePWD

 It is an attribute for the tabledef.  Not sure I understand why it's a problem...does your app compile.   if so, what references do you have?

Jim.
Avatar of StampIT

ASKER

It does compile. Do not know what you mean by "What references do you have?"
In the VBA editor, Tools, then references.

Jim.
Avatar of StampIT

ASKER

Visual Basic For Applications, Microsoft Access 16.0 Object Library, OLE Automation, and Microsoft DAO 3.6 Object Library.
You should not have DAO in the list.  Instead, you should have "Microsoft Office 16.0 Access database engine object library".

 Still the DAO reference should have worked.  That constant has been around for ages.

 But try the ACE version and see if that takes care of it.

Jim.
Avatar of StampIT

ASKER

For References I have Visual Basic For Applications, Microsoft 16.0 Object Library, and OLE Automation checked.

In the immediate window used  ?AttachDSNLessTable("Pot_Today", "dbo.Pot_Today", "servername", "databasename","","") to execute the code. Received a "Compile error: User defined type not defined" message.

What is the "ACE version"

Can I use the utility created by Dale to create a DSN-less connection ?

Thanks
Yes, all of the connections you create to SQL Server tables using my application will be DSN-less.

But as I said before, you will have to do that from a computer on the clients network which as Access installed.  As it is, you cannot run the code from a run-time application.
<<What is the "ACE version">>

"Microsoft Office 16.0 Access database engine object library".

 Still though it should not make a difference.  That constant is in both libs.

 If you want to post a small sample DB where it fails, I'll figure out what's going on.

Jim.
Avatar of StampIT

ASKER

Jim,

Have attached a small database. Appreciate your help. Thanks.
DSN_Less_EE.accdb
So in the DB you posted, you hadn't set a reference for the ACE Engine lib.   Here's what it should look like:

User generated image
 Your db did not initially compile, but after adding the reference it did.  Also note the debug window, which now shows that a value is seen for dbAttachSavePWD.

Also if you hit F2 (or click the object browser button) and then select the drop down for the library and select "DAO", which is the lib for this, you will see the constant as part of that:

User generated image
 Note the lib reference at the bottom.

 As I said, this should have worked with the DAO 3.6 reference you had set earlier as well.   But the ACE reference is really the one you want.

Jim.
Avatar of StampIT

ASKER

Jim,

Still get the invalid argument error. See attached Word doc. Have the references set up correctly, I think. However cannot see anything DAO in the object browser.

By the way how do you copy and paste images into the body of the comment. I cannot so I attach files.

Thanks.
EE_References_DAO.docx
Wrong drop down in the object browser.  It's the top one that selects the lib.   But in any case, you should be able to compile without error, no?

Jim.
Avatar of StampIT

ASKER

Yes. It compiles OK.

OK. Using correct drop down I see the DAO and dbAttachSavePWD attribute.
<<OK. Using correct drop down I see the DAO and dbAttachSavePWD attribute.>>

 and your still getting an error?   I must  not be understanding something.

Jim.
Avatar of StampIT

ASKER

Yes still getting "AttachDSNLessTable encountered an unexpected error: Invalid argument".

Using the following in the Immediate window:
?AttachDSNLessTable("dbo_Pot_today","dbo.Pot_today","SqlServer","ERPDatabase","","")
There is a bug in that code.  The declare is:

Function AttachDSNLessTable(stLocalTableName As String, stRemotTableName As String, stServer As String, _
            stDatabase As String, Optional stUsername, Optional stPassword As String)

when it should be:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, _
            stDatabase As String, Optional stUsername, Optional stPassword As String)

'e' is missing in remote table name, so it ends up blank when you execute the CreateTable().

Jim.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of StampIT

ASKER

Jim - Finally the code created the linked table. Thanks for all your work. Helped me understand how this all works. Where is Option Explicit ? and what does it do ?

Dale - Your utility works great. Thanks for the help.
<<Where is Option Explicit ? and what does it do ?>>

 Option explicit should be used at the top of every module and it ensures that every variable is actually declared, otherwise you get a compile error.

 Really helps with variable name mis-spells and with variable typing.

Jim.
Avatar of StampIT

ASKER

Thanks. Good to know.
I hope you gave Jim points for all the assistance he provided you.

If not, please go back and do so!
Avatar of StampIT

ASKER

Yes. I tried to. Is there a way I can check ?
Yes, one of my comments was tagged as an assist, but no worries anyway as I'm not here for the points.

Jim.
"I'm not here for the points."

I know, Jim, but it is about, especially in this case where you did 95% of the back and forth and actually taught StampIT how to do the relinking, making sure that others who use this question as a reference see that my LTM tool is only part of the solution, and if they want to add this feature into their applications, the assistance you provided was far more valuable than what I provided.

All I did was provided a link to my LTM tool.
@Dale,

  I was remiss in saying that I appreciated you bringing up the point though.  And as far as I'm concerned, that LTM utility took a heck of a lot more time than what I posted here!

 Have a good day and enjoy the long weekend.

Jim.