Advise: Use DSN-less or ODBC? and Access to Excel.

[Bob Ford]

I was asked to create and setup a database from ms access using sql server.
my suggestion is to use a dsn-less connection, in case another user need a
copy of the db, there is no hassle to create and setup odbc. am i right
with this analogy? will there be performance issues?

also, what is the proper way to update monthly columns values from ms access
to excel? i have a vba code that opens an instance of excel and uses
copyrecordset to output the updated values. but my concern is if multiple
users modify the monthly data from ms access, this will trigger to run the
excel update and that one excel file will lock users out from updating
spreadsheet.

pls help. thank you.

 

[Tom Wickerath]

Here is a web page written by Access MVP Doug Steele:

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html


I've never heard of a performance hit for using a DSN-Less (some say DSN
Free) connection.

On the issue of updating column values in Excel, are you talking one shared
Excel spreadsheet? Or, is a separate copy of the spreadsheet on each user's
PC?

this will trigger to run the excel update

What is the "this" that will trigger to run the excel update? A VBA
procedure? If so, how is it run? Also, do you need to update an existing
spreadsheet, or can you just replace it using DoCmd.TransferSpreadsheet
(assuming it is not in use, but that's a trappable error)?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________