Referesh my memory

[citychap26]

Hi Guys,

It's been a few years since I've used SQL Server/ Sybase...I may have to write an application with SQL Server as a back end and MS Access as a front end. I was aiming to write stored procs on the back end which I would call using ADO.

I have a few questions...

1. I need to have a data loading functionality, I'm guessing I could call bcp from MS Access?

2. Should I connect on demand to the back end or open a global connection that is always open?

Cheers

SK

 

[mrdenny]

1. You can use the BULK INSERT T/SQL command (however you'll need rights to use this). You can also use BCP from the command line.

2. I prefer to close the connection and reopen it. While this can add a little more load to the SQL Server it makes it easier for the app to recover if the SQL Server is restarted while the user is using the app. It also prevents a users work from getting locked up in a single large transaction and blocking all the other users.

Not to mention that if I need to kill a users session for some reason if it reconnects for each command already they can continue working right away.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com