We help IT Professionals succeed at work.

Access 2007 Conversion

BradleyCleveland
on
944 Views
Last Modified: 2013-12-05
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

Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Lots of questions ... these links should help you:

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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


CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
??
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.