Connect multiple access DB's to one ODBC connection? 1

[Lost500]

Howdy,
I have a multiple access databases that all point to a sql DB on a seperate server. right now everyone has their own datasource via ODBC.

is it ok to instead point all of the access DB's to a single ODBC datasource on the network? in short, can two computers utilize the same file datasource at the same time? and does that have a limit?

thanks in advance!

 

[Hap007]

Hi Lost,

Yes, if I read and understand your question, you can define 1 ODBC connection to a Network SQL database and then use that ODBC connection in as many Access Databases as you would like.

I am assuming that the local Access Databases are FrontEnds that have forms, reports, queries and code and you are just linking to the data.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Lost500]

Thanks Hap

that is what i was asking about i am conserned with multiple users utilizing the same ODBC connection at the same time.

I'm not familiar with the intricacies associated with ODBC connections i.e. how many users they can handle at one time or if it will get confused with multiple users calling different reports from the same sql database.

any direction would be great thanks

 

[Hap007]

If you are going to have Multiple users Log on to the same PC, then when you create the ODBC connection, in the ODBC Administrator window, select System DSN, and use that tab to create the ODBC connection. This way, no matter who logs on to the system, the ODBC connection will be defined and available.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Lost500]

thanks hap

I have a sql database on a network with multiple access front ends that users run on thier seperate computers so if i have a ODBC connection defined in a shared folder on the network can two people on two different computers open thier own copy of the access database and use the same connection to get to the sql linked tables? sorry if this is confusing thanks for the help.

 

[Hap007]

Hi Lost,

I am not sure if would work (give it a try), but why would you ever want to do that? You would be increase the traffic on the network server and slow down the speed of a SQL Server Backend by going thru an Access intermediate DB.

Just go directly to the SQL data.

Why not just create an ODBC Link from each user's PC to the SQL database directly. This would be much more efficient and provide the fastest results.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Lost500]

lol thats what i was thinking i was wondering if there were any other options thanks hap....