Avatar of StampIT
StampIT
Flag 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
DatabasesMicrosoft Access* ODBCSQL

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
Jim Dettman (EE MVE)

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.
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.
Jim Dettman (EE MVE)

<<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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Fye

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).
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.
Qlemo

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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\ ?
Qlemo

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).
Dale Fye

generally, most people have the 32 bit version of Office.  
Office versionEvery 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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
StampIT

ASKER
Dale - Yes. Copied from your article.
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.
Jim Dettman (EE MVE)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Jim Dettman (EE MVE)

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.
StampIT

ASKER
It does compile. Do not know what you mean by "What references do you have?"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jim Dettman (EE MVE)

In the VBA editor, Tools, then references.

Jim.
StampIT

ASKER
Visual Basic For Applications, Microsoft Access 16.0 Object Library, OLE Automation, and Microsoft DAO 3.6 Object Library.
Jim Dettman (EE MVE)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Dale Fye

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.
Jim Dettman (EE MVE)

<<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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
StampIT

ASKER
Jim,

Have attached a small database. Appreciate your help. Thanks.
DSN_Less_EE.accdb
Jim Dettman (EE MVE)

So in the DB you posted, you hadn't set a reference for the ACE Engine lib.   Here's what it should look like:

Region-Capture.jpg
 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:

Region-Capture.jpg
 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.
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

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.
StampIT

ASKER
Yes. It compiles OK.

OK. Using correct drop down I see the DAO and dbAttachSavePWD attribute.
Jim Dettman (EE MVE)

<<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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
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","","")
Jim Dettman (EE MVE)

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

<<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.
StampIT

ASKER
Thanks. Good to know.
Dale Fye

I hope you gave Jim points for all the assistance he provided you.

If not, please go back and do so!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
StampIT

ASKER
Yes. I tried to. Is there a way I can check ?
Jim Dettman (EE MVE)

Yes, one of my comments was tagged as an assist, but no worries anyway as I'm not here for the points.

Jim.
Dale Fye

"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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

@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.