Exclusing Connection to SQL with ADO

[arnierudy]

I am writing an application to migrate data from Access to SQL using VB6 and ADO. According to all the documentation I can find, I can force the user to open the databases exclusively by setting the Mode property of the connections to adModeShareExclusive before opening them.

This works fine for the Access database but not the SQL database. I have opened the SQL database with Enterprise Manager or Access 2000, and even had a co-worker open the database on a network server. When I then open the same database in code, I do not get an error and I can add records. This is the connection string:

&quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=<DatabaseName>;Data Source=<ServerName>&quot;

Is it necessary to use a user name and password in order to make the adModeShareExclusive work? If not, can anybody suggest what I'm doing wrong?

Thanks

 

[RiverGuy]

Are you just wanting to import data once, or specific times a day, or whenever a record is entered into Access? If its specific times, or even just once, you might want to just use a DTS package.

 

[arnierudy]

My utility will be used to port our customer's data from Access to SQL. We are providing the script to create the SQL database and a utility to copy the data. We will not be there at all our customer's sites when it is done. We are also making some changes in the SQL data structure. We have to do this in code so we can control it and I want to lock it down as much as possible. That's why I want to open the databases in exclusive mode. I can do it with the Jet provider, but not with the SQL provider and that's what I don't understand.

 

[RiverGuy]

So you're saying you have code already that updates the SQL Server, and you want to make sure that someone, when updating Access, doesn't send another transaction to the SQL Server to update it? I'm not really sure about that.

 

[arnierudy]

RiverGuy,

I appreciate your interest. You are correct. I have written code to update an empty SQL database with data from Access. What I need to do is make sure that nobody else can go into that SQl database and add or edit data while my code is running. Setting the Mode property of the connection to adModeShareExclusive is supposed to do that, but it is not working for me. I have tried both windows and SQL authentication and had another person log into the database on my machine while I ran my code but I was still able to open the connection. I not only need to keep others out after my code starts; I also need to have my code show a message if someone else is already in the database when the person running my utility tries to open the connection. Any suggestions would be appreciated.

Thanks.