Reading AND Writing Access 2 with SSIS

Question

I went for a job interview this morning, and one of the scenarios that the IT manager (a former programmer) told me about was the following .... If anyone could give me any pointers that I can email him pointers to tomorrow (Wednesday 22nd) it may just help tip his decision in my direction. So there's beer in it if you answer a relevent answer, and I get the job.

Old systems using Access 2 databases. These systems are not due to be updated any time soon.

New systems need access to that data, so each night he exports information using SSIS. However, he needs to also write data back, and more often than just overnight.

However, when he connects to the database, using ODBC connections I think, he says that the app (SSIS) opens the tables / database exclusively, even if he selects the option NOT to open it exclusively.

Any ideas, suggestions or comments ?

Answer 1

Could it be that the Access db itself is set up to open exclusively. Gosh it's been about a million years since I used Access 2.

Another possibility to fix is to consider making the SQl server tables that mimic the access tables and making them linked tables in access and using them instead of access. I can't remember if you can do this with Access 2 or not. If you can do this, then you will be making changes directly to the sql servver tables and not locking them up in Access 2. It's at least something to try. The use of linked tables should't break anything in Access if they have the same names and all tables havea primary key. ANd then you can performance tune as well if there are problems.

I'm throwing out ideas here, up to you to research the feasibilty before sending them on. But honestly, do you really really want to work for an organization that still uses Access2? If they haven't upgraded that, what else are they years out of date on? Access2 came out in 1993. So they haven't upgraded it in 17 years, scary stuff.