• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

revisiting upsizing from access to sql server

I am in desperate need right now. I have to upsize an access database to sql server. I ran the upsizing wizard, then created and obdc dsn and then linked the tables. When I tried to run the application, I got an error - #9 module load link permanent file - subscript out of range.

I have no idea how to begin troubleshooting this stuff. Please help! Time is short for me right now.

Pertinent info:
There are two mdb files - one for the data one for the front end. I upsized the data file and linked the tables to it. The error occurred when I tried to run the front end.
0
terriramos
Asked:
terriramos
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
arbertCommented:
"When I tried to run the application, I got an error - #9 module load link permanent file - subscript out of range."

Have you stepped through the VBA code to see what code is actually generating the error?
0
 
puranik_pCommented:
Bad dates can cause an upsize attempt to fail. Bad dates result from the differences between the Access date data type and the SQL Server datetime data type. Access valid dates range from 1/01/100 to 12/31/9999, whereas SQL Server valid dates range from 1/01/1753 to 12/31/9999. If for some reason Access data contains dates earlier than 1/01/1753, SQL Server flags these dates as incompatible with the datetime data type, and doesn't convert the table completely. The error message reads Subscript out of range and Skipping table. If the table containing the bad dates is a master table in the dependency hierarchy, all detail tables that reference this master table also fail to convert.

copied from http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=6108
0
 
ctudorpriceCommented:
What version of Access are you running? I used XP/2002 initially and had so many problems that went away in 2003. THe XP/2002 upsizing wizard is buggy.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jonathan KellyCommented:
I think you may need to read some of the whitepapers on upsizing on the ms site.

In my experience, upsizing anything more than a very simple application to SQL Server will probably require some modifications to the application.

from MS Site
http://support.microsoft.com/default.aspx?scid=kb;en-us;325017
"Note that when you run the Upsizing Wizard, this is not a perfect process. There are differences and potential incompatibilities between Access databases and SQL Server databases. Included are differences in SQL dialects and data types. Although the Wizard can handle many of these differences and still convert objects correctly, the Wizard cannot handle all differences."

0
 
terriramosAuthor Commented:
This application is going to need a lot of tweaking to work because it is very complex and has tons of queries etc. I don't really have the expertise to do all that right now. But my work wants me to get this first step done. I guess the database has become so large that access can't handle it anymore.

I haven't worked with vba stuff in access much (learning as I go) what is the process to step through the code. How do I start that to see where we are failing?

About a year ago, I did this process to the same application and I was able to get it to work (that is to say, I could get the code to see the sql server tables) but because I am not proficient in vba and access, I didn't proceed with the project.

Thanks for responding so quickly.
0
 
Jonathan KellyCommented:
can you talk to the developeres. they should be able to tell you where to start.
0
 
arbertCommented:
Um, sounds like you have a lot of issues that can't be addressed here.  The company can't expect this to be done overnight and should invest in the right resources to get it done.

Have you told the company you're not proficient?
0
 
terriramosAuthor Commented:
Yes - they know what the deal is. The developers are gone and that leaves me holding the bag.

Thanks for your help and all the resource info.
0
 
ctudorpriceCommented:
terriamos,
good luck... I recently was stuck in a similar predicament but did manage to get through it.

I started, much as you seem to be, thinking that linking the tables via ODBC would be a good plan. Nope. Bad plan. I had a lot of problems (similar to you) and ended up combining the front-end and the back-end into a single MDB and upsizing the whole thing. Running the upsizing wizard on the front-end forms, reports etc. helped (can't remember what specific changes it made though - this was a while ago). The end result is a pretty clean .adp which you can work with and clean up.

The combined database had about 300 tables, about 250 access queries, 50 reports, 400 forms. The upsizing wizard in 2003 made it through the whole process without crashing (XP/2002 crashed often) and then produced a report indicating what could and could not be upsized (about 90% upsized successfully, most of the rest were simplish fixes). There are a whole bunch of things that it doesn't like, including references to forms/fields in queries (as criteria/parameters), use of Access user-defined functions in queries, and much more. Once the tables are upsized (not usually a big problem), there is a lot of manual work to do if the previous developers used Access-specific capabilities/functions (as above). I had to go through every query, form, report, etc. etc. and test each after I had recoded the non-upsizable queries, etc. It is a long process - but only took me three days to get the upsized database and the new front-end into production. I am no VBA or SQLServer guru either, but it's pretty straightforward really and I ended up with a very clean SQLServer/Access Data Project solution with (very fast) compiled SQLServer functions instead of Access queries, stored procedures... and the performance was blazing.

Anyway, I know you've allocated the points, but I feel for you! I hope my little anecdote helps.... sorry the details are a bit foggy.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now