Can I attach to SQL Server without ODBC?

[Matthew Speed]

I have an Access XP application that talks to a SQL Server 2000
database to generate some reports. The app does no data modification,
only retrieval. I set it up initially to use an ODBC datasource but I
am wondering if there is any way to attach the tables programmatically
as I need to deploy this to about 20 desktops and do not relish
setting up ODBC connections on every machine.

If this is possible, are there any negatives to this versus one using
ODBC?

(BTW...I am a VB6/VB.Net developer so I am not afraid of getting into
VBA code if needed. This is my first Access application so the
capabilities and restrictions of Access relative to VB6 are not
familiar to me.)

TIA

 

[Kent Dean]

Hi Matt

ODBC is fine.
You don't need to set up a DSN on each machine, as once a
table is connected, access stores connection string with
each table.

However you may wish to refresh the links programatically,
and you can do by setting up a sub which

a) sets a CurrrentDB object
b) Loops through the CurrentDB.TableDefs Collection,
modifies the .connect property and runs the .refreshlink
method of each table def.

I hope this helps

Kent