BradleyCleveland
asked on
Access 2007 Conversion
I have an Access 2k .adp database. It's a good size database. I use SQL 2000 Server for the backend. My boss wants me to convert to Access 2007. I have found out that Access 2007 does not support the .adp format.
The database is used by a healthcare organization. Therefore we track a lot data involving our clients.
Is the accdb the database I want to use?
Is there an "EASY" way to convert the VBA code (many, many, many lines of code)
In the .adp I used Stored Procedures and Views instead of queries. Can the SQL statements for these be pasted into queries in Access 2007?
Any pointers concerning the conversion?
Is Access 2007 stable? Any Service Packs in the foreseeable future?
How many users can open the Access 2007 front end before the system boggs down? Currently, I have about 80 users, but rarely have more than 30 to 35 active at one time. Believe it or not the Access 2K, Sql Server 2000 database works well with 35 users going at one time.
The db size is about 60Mb, there are a lot of reports with graphs. And, most of the forms are not simple. Most have tabs, with multiple sub forms.
Thanks
Tom
The database is used by a healthcare organization. Therefore we track a lot data involving our clients.
Is the accdb the database I want to use?
Is there an "EASY" way to convert the VBA code (many, many, many lines of code)
In the .adp I used Stored Procedures and Views instead of queries. Can the SQL statements for these be pasted into queries in Access 2007?
Any pointers concerning the conversion?
Is Access 2007 stable? Any Service Packs in the foreseeable future?
How many users can open the Access 2007 front end before the system boggs down? Currently, I have about 80 users, but rarely have more than 30 to 35 active at one time. Believe it or not the Access 2K, Sql Server 2000 database works well with 35 users going at one time.
The db size is about 60Mb, there are a lot of reports with graphs. And, most of the forms are not simple. Most have tabs, with multiple sub forms.
Thanks
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Is the accdb the database I want to use?"
Hard to say. Once you do ... and if you use any of the new features, you will not be able to convert back.
"Is there an "EASY" way to convert the VBA code (many, many, many lines of code)"
If you are talking about vba code .... that *should* convert without too much trouble (yeah, right!)
"How many users can open the Access 2007 front end before the system boggs down?"
Soooo many factors can affect that ...
"Is Access 2007 stable? "
It's a MAJOR new release ... many bugs ... see the bug list from my first post - which leads to several other bug list ... !!!!!
mx
Hard to say. Once you do ... and if you use any of the new features, you will not be able to convert back.
"Is there an "EASY" way to convert the VBA code (many, many, many lines of code)"
If you are talking about vba code .... that *should* convert without too much trouble (yeah, right!)
"How many users can open the Access 2007 front end before the system boggs down?"
Soooo many factors can affect that ...
"Is Access 2007 stable? "
It's a MAJOR new release ... many bugs ... see the bug list from my first post - which leads to several other bug list ... !!!!!
mx
Converting to an accdb??
An adp uses ADO and does not have tables & queries (these are all in SQL Server). MS flagged DAPO was on the way out circa Access 2000/2002, but 2007 is based on a new version of DAO. Whilst ADO code should work there'll be some fun redining server connections away from the OLEDB one that an adpo uses back to ODBC which 2007 uses. It may be possible to stick with OLEDB and dsn- less connections - I've never been game to try - a few articles on this.
Unless you've a real need for the new features (and some don't work via SQL server - rich text for instance), I'd try and see if you adp will run directly - you may need to get hold of a 2002/2003 version of Access and convert the adp to that file type - there'll not be any problems there.
An adp uses ADO and does not have tables & queries (these are all in SQL Server). MS flagged DAPO was on the way out circa Access 2000/2002, but 2007 is based on a new version of DAO. Whilst ADO code should work there'll be some fun redining server connections away from the OLEDB one that an adpo uses back to ODBC which 2007 uses. It may be possible to stick with OLEDB and dsn- less connections - I've never been game to try - a few articles on this.
Unless you've a real need for the new features (and some don't work via SQL server - rich text for instance), I'd try and see if you adp will run directly - you may need to get hold of a 2002/2003 version of Access and convert the adp to that file type - there'll not be any problems there.
ASKER
Can you point me to sites that might help me connect Access 2007 to SQL Server 2000? I went through some of the help screens, and seems I needs something called DNS(??). I have tables, view, and SP's in an existing SQL database. In my Access 2000 .adp, it's real simple, I click File, Connection, change a couple of things, and shazam! I'm connected.
Thanks
Tom
Thanks
Tom
http://msdn2.microsoft.com/en-us/library/bb203849.aspx 'Transisitioning to A2007
http://blogs.msdn.com/access/archive/2006/06/05/618366.aspx 'A2007 Limitations list
http://allenbrowne.com/Access2007.html ' A bunch of good stuff
http://allenbrowne.com/Access2007.html#Bugs 'A2007 bug list