Do I need to setup an ODBC connection in the windows ODBC Data Sources to use ODBC in access?

[Vita]

Hi, Basically what the title says.
An application I am developing uses

DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, Tablename(i), Tablename(i)

and I was wondering if I need to go onto every computer that will use it and setup an ODBC connection in the windows ODBC Data Sources or if maybe I could use a different method of retrieving the tables and setting them up as linked tables?



I don't believe it is an issue with the connection string as when I setup a connection inside ODBC Data Sources everything worked fine but regardless here is my connection string:

strConnect = "ODBC;Driver={ODBC Driver 11 for SQL Server};Server=ServerName;Database=DBname;Network=DBNMPNTW;UID=UID;PWD=PWD;"

The TableName(i) is just to cycle through a multidimensional variable(if that's what you call it? please correct my terminology if I'm wrong)

Dim TableName(0 To 6) As String

Thanks!

[ranman256]

windows start,
ODBC
add the odbc connection details (say: MyOdbc)
then in access, external data
get SQL server (or other db)
Link tables
choose MyOdbc.
select all the tables you want to use.

[Vita]

windows start,
ODBC
add the odbc connection details (say: MyOdbc)
then in access, external data
get SQL server (or other db)
Link tables
choose MyOdbc.
select all the tables you want to use.

I was asking if I need to add the connection details in windows or if I can set it up in access so that it doesn't need that because doing that on every computer is a hassle and it seems like there would be a different way.

[June7]

Need on each computer.

[Vita]

Need on each computer.

Yes? Am I phrasing something wrong here? or misunderstanding?
If I use

Driver={sql server}

instead it seems to work between computers perfectly fine but I was informed I shouldn't do this?

Edit: Not intending to be rude. I am just trying to figure out a way to do this so that I don't have to go to 50+ computers and setup an odbc connection. That is why I have been trying to use a DSN-less connection.

[June7]

Sorry, yes, if you want to use DSN-less and VBA code to establish connection, that is possible. I have that setup on my laptop. I am not sure what the drawbacks could be on a LAN.

[Gicu]

You do not need to set up a DSN on each computer for sure; but what you need is to have the same driver installed on each computer. "SQL Server" driver is the legacy driver that comes with Windows and is installed by default on each Windows machine, that is why it works. But it is a very old driver and most developers recommend using one of the latest ones.
Cheers,

[Minty]

As per Gicu above, I would install a later driver than the default one. You will need that on every client machine.

You can write code that can interrogate the drivers that are installed on a machine and set the connection string appropriately, but it's much easier to have a consistent driver, and avoid any issues with compatibility and performance.
In a corporate environment you can push out a suitable driver using group policy.

We use Version 17 and are slowly migrating to version 18 where we can with our clients.

[Vita]

You do not need to set up a DSN on each computer for sure; but what you need is to have the same driver installed on each computer. "SQL Server" driver is the legacy driver that comes with Windows and is installed by default on each Windows machine, that is why it works. But it is a very old driver and most developers recommend using one of the latest ones.
Cheers,

So that's the weird thing. When I open ODBC Data Sources and click add, they both have "SQL Server Native Client 11.0" Version 2011.110.7462.06 with the same file name and date.
Yet When I was using "Driver={ODBC Driver 11 for SQL Server}" It worked on the development computer but not the test computer. I was getting an error 3151 ODBC Connection failed. Changing to "SQL Server" has since fixed the issue.

As per Gicu above, I would install a later driver than the default one. You will need that on every client machine.

You can write code that can interrogate the drivers that are installed on a machine and set the connection string appropriately, but it's much easier to have a consistent driver, and avoid any issues with compatibility and performance.
In a corporate environment you can push out a suitable driver using group policy.

We use Version 17 and are slowly migrating to version 18 where we can with our clients.

I am very interested on how to do this. I don't plan on integrating it but it sounds interesting!

[Vita]

Sorry, yes, if you want to use DSN-less and VBA code to establish connection, that is possible. I have that setup on my laptop. I am not sure what the drawbacks could be on a LAN.

I thought I responded to this with the above info but I guess I forgot, my bad! What do you mean by drawbacks on LAN? I know there are some differences but I feel like just file sharing via ODBC would act the same as an internet connection no?

[Gicu]

So that's the weird thing. When I open ODBC Data Sources and click add, they both have "SQL Server Native Client 11.0" Version 2011.110.7462.06 with the same file name and date.
Yet When I was using "Driver={ODBC Driver 11 for SQL Server}" It worked on the development computer but not the test computer. I was getting an error 3151 ODBC Connection failed. Changing to "SQL Server" has since fixed the issue.

Those are two different drivers:
https://social.msdn.microsoft.com/Fo...qldriverforphp

[Vita]

Those are two different drivers:
https://social.msdn.microsoft.com/Fo...qldriverforphp

You know what, I didn't even notice the development computer had that ODBC driver installed. I was so hyper focused on using the SQL Server 11 one because that's what I was told is on every computer and then I misused connectionstrings.com I guess. Can I just enter the name of that driver into the brackets and it will select it?

[Minty]

The driver has to be installed on the host computer, it won't just work by changing the connection string.
The ones I currently check / use depending on the age of the app are;

"SQL Server Native Client 11.0"
"ODBC Driver 11 for SQL Server"
"ODBC Driver 13 for SQL Server"
"ODBC Driver 17 for SQL Server"
"ODBC Driver 18 for SQL Server"


I can't share the code unfortunately as it belongs to a client, but it does involve enumerating through registry entries.

[Vita]

The driver has to be installed on the host computer, it won't just work by changing the connection string.
The ones I currently check / use depending on the age of the app are;

"SQL Server Native Client 11.0"
"ODBC Driver 11 for SQL Server"
"ODBC Driver 13 for SQL Server"
"ODBC Driver 17 for SQL Server"
"ODBC Driver 18 for SQL Server"


I can't share the code unfortunately as it belongs to a client, but it does involve enumerating through registry entries.

You can retrieve registry entries in Access? Would you be able to elaborate on how it functions at all? I imagine its just a for loop that enumerates through registry files to check if they are the same as any of the predefined driver names listed above.
I don't know enough about registry files though or how you would retrieve them.
I apologize if I am pushing a little, its just that this is interesting and opens my mind to so many other possibilities. Gotta get those tools for my toolbelt ya know? and thank you for whatever information you can give! I always learn great stuff here.

[Minty]

There is some useful pointers here:
https://devblogs.microsoft.com/scrip...on-a-computer/

and here
https://stackoverflow.com/questions/...led-in-windows