MS Access data conversion - Re: upsizing access 2002 database to SQL Server 2000

Author

Re: upsizing access 2002 database to SQL Server 2000 - fatal error

Jon

2005-04-20, 9:25 am

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

> 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

2005-04-25, 9:29 am

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

> 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
>
>

Alex White MCDBA MCSE

2005-04-25, 9:29 am

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.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

"Jon" <Jon@discussions.microsoft.com> wrote in message
news:A207A542-7396-4AC8-A661- 89B0BEB97940@microso
ft.com...[color=darkred]
> 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" wrote:
>

david epsom dot com dot au

2005-04-26, 8:25 pm

> 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)

"Jon" <Jon@discussions.microsoft.com> wrote in message
news:A207A542-7396-4AC8-A661- 89B0BEB97940@microso
ft.com...[color=darkred]
> 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" wrote:
>

Sponsored Links