Post #51,480
9/12/02 12:20:34 AM
|
Access migrations to SQL Server
I am trying to import tables to SQL Server from Access. I have been able to do it so far, but have had a few bugaboos:
#1 Tables got imported, but no indexes, no primary keys, no relationships, and Queries came out as mutant tables instead of stored procedures or views like I thought they would.
#2 Some stuff didn't import, but only 3 out of a few hundred tables did not convert due to some goofy data. Empty tables converted over fine.
#3 ODBC connections generated errors, UID connections seemed to work better.
#4 My boss is thinking of doing an Oracle, MySQL, Sybase, or other database later on. SQL Server has an expensive license fee per client, etc. Right now the current 3.X version of the custom software uses Excel (gag!) files for data, and the 4.0 version uses Access, and I am working on the SQL Server migration. I figure that using two ADO recordsets, I should be able to copy data to populate the SQl Server tables. Is there any way to synch up two different databases? So that an Access MDB is on the Workstation (as a database cache in case the Server connection is down?) and the SQL Server is on the Server and can send changes to the Access, and vica-versa. We use transaction tables (Excel files, Access Tables, etc) for now.
Any data migration tools out there that are open sourced, freeware, shareware, or low cost? It is a small company, barely able to afford the slow DSL connection for their WAN/VPN system. I had to download W2K, SQL Server, etc service packs at home and burn them to a CDR disk and take them to work. The Test Server is nicknamed "Homer" from the Simpsons, seems the System Admins have a thing for the Simpsons. :)
[link|http://games.speakeasy.net/data/files/khan.jpg|"Khan!!!" -Kirk]
|
Post #51,624
9/13/02 9:54:27 AM
|
Re: Access migrations to SQL Server
I don't have any answer for a do-it-all migration tool for Access to SQL Server, but having done a few migrations (Access -> SQL Server, SQL Server -> Oracle) I can say that there is no fool proof panacea migration tool out there. You simply have to lay out your options, and pick the best possible path.
For Access to SQL Server, I would just lay out the tables manually in SQL Server, and then use SQL Server's DTS (Data Transformation Services) to migrate the data to the newly created tables.
For the purposes of the migration, you may need to drop all FK constraints and re-add them after the data has been migrated, to avoid as many headaches as possible.
HTH,
-Jason
----
My pid is Inigo Montoya. You "killed -9" my parent process. Prepare to vi.
|
Post #51,819
9/15/02 3:38:07 PM
|
Agree w/Jason
Having done more than a few Access->Sql Server migrations, I've also found it best to create the database(s) on Sql Server manually and then use DTS to get the data over (the Access "Upsizing Wizard" is a POS imNSho).
As far as keeping local copies of the data and updating Sql Server later, if I understand the task at hand, there are basically two techniques. One is to make extensive use of disconnected recordsets and use the ADO UpdateBatch method (don't know if that fits or not) and the other is to use replication.
hth,
Mikem
|
Post #51,861
9/15/02 11:00:33 PM
|
ERWin
#1 Tables got imported, but no indexes, no primary keys, no relationships, and Queries came out as mutant tables instead of stored procedures or views like I thought they would. What I usually do is suck the model into ERWin, then export it to a SQL script. Clean up the script and then either execute it on the server or suck it back to ERWin and have it generate the schema on the target database.
|
Post #52,126
9/17/02 10:47:21 PM
|
I lemba ERWIN!
I just am now downloading a copy as we speak. I'll burn it to a CDR and then install it at my system at work.
Nothing like having 100+ tables to convert in an Access database to a SQL Server table. My coworker thinks I can do all of that in one day. Yeah, if I had a tool like ERWIN. I'm about ready to tell her to go ahead and show me if it can be done by hand in one day without working overtime. Sure, she thinks if I only spend 30 seconds per table that I can get it done. Yeah I could, if she doesn't mind indexes not set correctly, default values not set, and of course if I link up those 100 tables, I might miss a few table relationships as she b*tches at me for 20 minutes about doing things slightly different than the way she does it. Like SQL Server is going to act and look exactly like MS-Access does? Yeah right!
[link|http://games.speakeasy.net/data/files/khan.jpg|"Khan!!!" -Kirk]
|
Post #52,300
9/19/02 4:02:09 AM
|
Bloated Crapware, cannot connect to our SQL Server 2000
I would not waste my time buying ths bloated crapware from CA. It would not logn into our SQL Server 2000 database. I had the "Correct" password for the Sa and other accounts, but it kept giving me a stupid error that it was not a trusted account. Yet SQL Server 2000 Enterprise Manager works with the same user name and password. WTF? *Boggle*
Meanwhile a $50 USD tool at [link|http://www.ispirer.com/products/|[link|http://www.ispirer.com/products/|http://www.ispirer.com/products/]] might work better for me.
ERWIN was an evaluation, if it had f*cking worked I might have bought the stupid thing. Now I just uninstall it from my harddrive. It was a 36M download, I want my bandwidth back dammit! Ispirer's software is only 100K big, and cost a whole f*cking lot less. Guess which one I might give my credit card number to?
[link|http://games.speakeasy.net/data/files/khan.jpg|"Khan!!!" -Kirk]
|
Post #52,408
9/19/02 10:52:28 PM
|
Just a wild guess, ispar? :-)
The lawyers would mostly rather be what they are than get out of the way even if the cost was Hammerfall. - Jerry Pournelle
|
Post #52,431
9/20/02 1:07:38 AM
|
Haven't purchased it lately
But then I very rarely use ERWin for forward engineering - relying on it for reverse engineering in rare circumstances. I guess I'm of the old school - the database should be created by a script of SQL DDL statements. My approach would have been to suck in the Access database into ERWin and then push it out as a SQL Script. From that point forward, I'd use my text tools to modify and maintain the database. Text is much more pliable.
Anyhow, you got the idea. The particular tool is not what I was trying to promote so much as using a modelling tool to transfer the schema from one database to another. So, if there's another tool to do the same job, then at least the idea was correct.
As for ERWin, I think that CA is the antithesis to the concept of innovation. Buy software, stop development on the tools - then milk every last drop from those that got addicted to the tool. Not particularly one of my favorite companies.
There is a real need for a database modelling tool, but unfortunately the market wasn't very kind to the previous owners of ERWin. They were flying high back in the mid 90's with lots of capital to blow. I was involved in one deal where we were trying to get some investment dollars from them - it involved reverse engineering AS/400 databases via RPG. They were also looking at a bunch of Fortran and Cobol reverse engineering programs. In a matter of 6 months it seemed like they went from being ambitious, to mostly maintenance mode. Part of their problem was they spent a lot of money trying to integrate their tool with COM, but between the mismatch of their tool to the environment (modelling objects is much different to modelling entities - rational kicked their buts) and the inherent competition with Microsoft, they just gave up and sold out to CA.
If they had just stuck to database modelling, the tool would have been a whole lot better.
|
Post #52,578
9/21/02 12:22:11 AM
|
First Clue.
"CA, where software goes to die." I used Erwin right before CA bought it, had the last rev before it became CA Erwin. Computer Associates can destroy anything, anytime, anywhere. You can, believe it or not, write a VB app that will do the conversion you want to do. You'll have to know DAO, ADO and DMO, but it can be done. I stand by my earlier post, but if you want help writing an app, lemme know. I'm busier than I'd like to be, but this is one area I know well and I'd be glad to help.
bcnu,
Mikem, M.S., MCSD ;-) How's that for b.s.!
|
Post #52,652
9/21/02 7:03:41 PM
|
Yeah I want to do that
write the VB app using DTS in SQL Server to do the conversion; however, my coworker is dead set against that and turns a bright hue of red when I mention using VB to do database migration. WTF should I do that when I can do it by hand like she is doing in Access when she goes from one Access version to another? Like I said before, I am seriously considering becoming a Corn Farmer and get paid not to grow corn, and then use the money to develop open sourced products to put my former employers out of business. ;)
[link|http://games.speakeasy.net/data/files/khan.jpg|"Khan!!!" -Kirk]
|
