Mike, This is exactly how I coded, as the location of the other database might change over time. In fact, they are now switching to the Express (free) version of MS SQL Server and I will have to change the driver, otherwise the code should work just fine. DSN-less is definitely the ways to go.
Javier, Javier Valencia, PE 913-829-0888 Office 913-915-3137 Cell 913-649-2904 Fax [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Mike Byerley Sent: Tuesday, March 22, 2011 9:08 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Collecting data from an Access DB To reiterate an old saw.. use an DSN-less connection instead of DSN for reliability and flexibility. You can keep the physical location of the db to be SCONNECTed in the RBase db and build the connection string dynamically, so IF the location of the db to be SCONNECTed ever changes, you don't have to go to every workstation to change the DSN. ----- Original Message ----- From: "Javier Valencia" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Tuesday, March 22, 2011 2:08 AM Subject: [RBASE-L] - Collecting data from an Access DB > One of my clients has updated its Fuel Managements System. The system > collects, among other things, odometer and hour meter every time the > vehicle > is fueled and also every time the vehicle drives through the gate. The > most > current information is kept in "vehicle" table in an Access database. > > The R:Base based Fleet Management System collects fuel transaction and > meter > information every night at midnight from a downloaded file generated by > the > Fuel Management System. > > Now, the client wants to be able to see the most recent information any > time > a vehicle comes in for servicing or any time the information is needed for > other reason. > > Right now, the procedure to get the most recent odometer and meter > information is: > > SCONNECT > SATTACH vehicle table > Select the information from the SATTACHED table > SDETACH vehicle table > SDISCONNECT > > The query will be run dozens of times during the day from several > workstations. > > My questions are: > > Should I run the procedure outlined above every time? > > Since the connection is global, should I SCONNECT and SATTACH once, and > leave the connection on, and have users run the SELECT query on the > attached > table when needed? Are there issues leaving the connection on permanently? > > Should I SCONNECT AND SATTACH when the first user logs into the system and > SDETACH AND SDISCONNECT when the last user exist the system? > > Any advice, suggestion and words of wisdom will be appreciated. > > Javier, > > Javier Valencia, PE > 913-829-0888 Office > 913-915-3137 Cell > 913-649-2904 Fax > [email protected] > >
