This is a partial cross-post from thread705-1604429.
But I am curious what others think and having it in a seperate thread will make it easier to find.
My reasons for choosing ADP vs. MDB with Linked tables.
1. I do not have the ability to modify every workstations DSN list to include all the Access DB's that will be migrated to SQL Server. (60+)
These are government workstations and require a lot of red tape to make that sweeping of a change especially to DB Access.
2. I create temp tables for parsing data like tracking numbers. With multiple people needing access to these temp tables (they exist for one month) I couldn't find an easy way to make sure the old table was dropped from the SQL server the new table created and the data parsed and a new linked table created and available to all the users.
With the ADP, this is greatly simplified and DB schemas keep different departments from even seeing tables, queries, functions, and stored procedures in the AccessUI without any programming or naming conventions to "hide" tables. If an individual should not access the table or what ever the DB security takes care of it and it is dynamic. Changes on the SQL server are applied immediately.
This ability will also allow me to gradually merge the rest of the Access databases into one encompassing Application while keeping it departmentally compartmentalized.
3. If the user does not have create rights in the DB they CANNOT create new queries, etc. This helps keeping the users from "adding" functionality to the Access Application without any oversight. (A common problem with the existing environment.)
With that said.
I wish ...
1. VBA programming for an Access ADP project was better supported/documented by MS.
2. MS documented the differences better.
Also, as a new user to programming/creating Access Applications, I have spent a fair amount of time over the past year trying both options and prefer to work with ADPs when dealing with MS SQL server as the backend. Even with the fact the current environment I am dealing with is MS Access 2003 and SQL Server 2005, which means that all SQL development cannot be done in Access.
I do hope that MS puts more development time into supporting and updating Access project applications.
I also, should note that I have read a fair amount of threads like this one.
thread958-1528961: Move from Jet to ADP?
That recommend not using ADP's.
Thanks
John Fuhrman
But I am curious what others think and having it in a seperate thread will make it easier to find.
My reasons for choosing ADP vs. MDB with Linked tables.
1. I do not have the ability to modify every workstations DSN list to include all the Access DB's that will be migrated to SQL Server. (60+)
These are government workstations and require a lot of red tape to make that sweeping of a change especially to DB Access.
2. I create temp tables for parsing data like tracking numbers. With multiple people needing access to these temp tables (they exist for one month) I couldn't find an easy way to make sure the old table was dropped from the SQL server the new table created and the data parsed and a new linked table created and available to all the users.
With the ADP, this is greatly simplified and DB schemas keep different departments from even seeing tables, queries, functions, and stored procedures in the AccessUI without any programming or naming conventions to "hide" tables. If an individual should not access the table or what ever the DB security takes care of it and it is dynamic. Changes on the SQL server are applied immediately.
This ability will also allow me to gradually merge the rest of the Access databases into one encompassing Application while keeping it departmentally compartmentalized.
3. If the user does not have create rights in the DB they CANNOT create new queries, etc. This helps keeping the users from "adding" functionality to the Access Application without any oversight. (A common problem with the existing environment.)
With that said.
I wish ...
1. VBA programming for an Access ADP project was better supported/documented by MS.
2. MS documented the differences better.
Also, as a new user to programming/creating Access Applications, I have spent a fair amount of time over the past year trying both options and prefer to work with ADPs when dealing with MS SQL server as the backend. Even with the fact the current environment I am dealing with is MS Access 2003 and SQL Server 2005, which means that all SQL development cannot be done in Access.
I do hope that MS puts more development time into supporting and updating Access project applications.
I also, should note that I have read a fair amount of threads like this one.
thread958-1528961: Move from Jet to ADP?
That recommend not using ADP's.
Thanks
John Fuhrman
