upsizing access 2002 database to SQL Server 2000 - fatal errors

[Jon]

Hi,
I am trying to upsize an access database (about 200meg compacted) to sql
server 2000 using the upsizing tool. The wizard does not complete and kicks
me out asking to send the error report to microsoft. the error in event
viewer is:

Faulting application msaccess.exe, version 10.0.6501.0, faulting module
msjet40.dll, version 4.0.8618.0, fault address 0x000d10cb

can anyone point me in the right direction?

Thanks,

Jon

 

[Jon]

I should also have said this happens when creating extending properties for
one of the tables

Thanks,

Jon

 

[Alex]

Hi Jon,

Have you tried to upsize without importing the data?

Most of the time it fails because of bad data, but your error is not
being trapped as it is closing access, make sure you have the latest
fixes and updates for office http://office.microsoft.com goto to 'check
for updates' top right hand side. Also make sure you have the latest
MDAC on your computer http://www.microsoft.com/data.

Is the SQL server located on your computer you are running the upsizing
wizard?

Make sure that you are running SP3 for SQL server as well
http://www.microsoft.com/sql

If all else fails try and upsize everything else other than the
offending table, and you may have to create and import manually. After
updating all the components do a compact and repair on you database,
also try creating a blank database and importing everything from the
problematic database into the new one and try and upsize that.

Hope it helps.

Regards

Alex

 

[Jon]

Hi Alex,

Thanks for that, few things for me to try there.

I have tried it in several environments (running on cirtix server with a
serarate sql server and also on my pc running win 2k server with sql server
and ms office xp locally)

I have just tried the wizard without importing the data (on the citrix
environment) but I dont think we are MDAC'd and sql service packed up on that.

I will try updating my pc and running it on that (which was getting the same
error), should the fact its not the same spec cause errors or would it just
mean things would happen slower?

Also we were planning on upsizing to a sql server back end and microsoft
access project front end, do you have any links which tell me how reporting
works in this kind of set up, do you build the report in access and just link
this through to a stored procedure in sql server? bit of a novice with access
i'm afraid?

Thanks,

Jon

 

[Alex]

Hi Jon,


Speed of the computer will just mean things will go slower until you
start reaching timeouts.

What are you using on the front-end for reporting if you are using
access type reports they will function as normal, just import them over
to the ADP project, you can use the same type of system as access in
linking the direct tables to reports, but for performance reasons move
your data processing off the workstations and onto the server.
Personally I don't use SQL views as I always opt for stored procedures
as they give me more functionality in the long run.

To get you started, get your data over to SQL then create an ADP
project linking to that SQL server database, then import your
forms,reports and modules into the new ADP from you old MDB, just to
see how it works. It is a good move to learn something about how to use
SQL in relation to stored procedures and triggers up front as to learn
these afterwards could mean undoing a lot of work.

Just to give you some advice tiggers are the system that keep your
business rules running on SQL, e.g. update the contacts table, write an
audit log in another table, easy business for SQL etc. MOVE YOUR
PROCESSING TO THE SERVER.

Hope that helps a bit, for references http://msdn.microsoft.com for
documentation on stored procedures and triggers.

Regards

Alex

 

[Jon]

Hi Alex,

If your still about could this be something to do with bit fields in the
access database?
I have read on a few sites that sql server doesnt like empty bit (i.e.
yes/no) fields when upsizing data. would this cause an error or would
upsizing complete and then tell you at the end?
I have tried importing everything bar the data into a new access database,
sorted out the references so that the thing actually loads and upsizing that
and it seems to work..could this be the problem? if so would just updating
the design of the affected tables to have a default value and to show its
required sort it out?

Cheers,

Jon

 

[Alex White MCDBA MCSE]

Hi Jon,

Never seen problems with bit fields myself, but if you believe that empty
bit fields could be your problem, then create an update query on your access
data to set all the null bit fields to 0, there is a difference in the way
access and sql interpret bit fields but this have never been a problem. You
probably have a specific table that keeps failing try the modifications on
that first, then try to import the data. In the past I have had so many
problems with the importing of data that I have written my own importing
routines. If you want these you are welcome but, they are written in VB.net,
v2 if you have the compiler you are most welcome to have a copy.

Give that little lot a go post back here if you require any more help.

 

[david epsom dot com dot au]

I have read on a few sites that sql server doesnt like empty bit

yes/no) fields when upsizing data. would this cause an error or

SQL Server couldn't do primary key indexes which included
bit fields.

If you haven't done this before, at least have a look at
this commercial product - even the free download is useful:
http://www.ssw.com.au/ssw/UpsizingPro/


(david)