Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lester Vincent
Starting Member
22 Posts |
 Posted - 2006-01-12 : 19:23:18
|
 Hi people. I have upsized an app to SQL Server 2000 and busily writing Stored Procs, UDFs etc to replace VBA Code. The app is extensive, with approx 150 tables and is currently running in Access 2K on 12 workstations, linked to tables .MDB files on the Server. (Running slow, as you'd expect, but now with 12 months data).I am developing off-line, using SQL Server tables loaded with "practice data". The new Access front-end will be linked only to a workstation-based MDB file of static lookup tables (refreshed from SQLS at boot-up time) and links to SQL Server data will be via ADO Connection object. At "cutover" time, I will have to delete contents of most of the SQLS tables (static lookups are a possible exception) and quickly import "live" data from the currently used "live" Access tables.The worksite is very busy, and I expect some solid time limits on doing the cutover. I may have to re-delete from the SQL Server tables and refill them a second time, depending on how it goes initially.At the moment, I look like having to individually open up each SQLS table and delete the rows, or delete the table and re-import it via DTS.Not so worried about the queries, as I'm hoping to replace most of them as redundant. I need a neat, quick way to achieve this, if anyone can help. I'm sure many of you have been through this before.Regards,Lester Vincent  Sydney |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-13 : 06:33:30
|
Can you change the existing Access App. so that it checks a SQL Database (or some other central, external, source) for a "version number" and then performs action(s) depending on that?So when it finds that the "remote version" is, say, "3" and locally its at "1" it needs to perform actions "2" and "3" to get up to date.The "actions" could be a list of SQL Statements - again available at some central/remote location.They could be dynamic SQL for "Drop local tables ..." type stuff I expect?Then on the day you just set the "new version" on the central/remote source and all the applications update themselves!You would need the means of testing that the rollout actually works in practice, of course  Kristen |
 |
|
|
Lester Vincent
Starting Member
22 Posts |
Posted - 2006-01-13 : 17:50:01
|
| Thanks Kristen. I hope it can be done like that. Sounds simple, but I am hoping someone has a way they have tried, that has worked for them. I am fairly new to T-SQL, but learning fast, as I have to. A couple of points might help.1. Both the Server and workstations are all at the one site.2. It would not matter if data in all tables were replaced in the one batch, as the workstation-based tables only hold "local" copies of some of the Server tables, and these are refreshed at boot-up time.3 Both the Access tables files and the SQL Server files are located on the same machine - possibly on different HDDs there, but that may change over time. I hope you are spot-on with your suggestion. At this stage, I have no way of looping through the tables to (a) empty and fill them, or (b) delete and re-import them from the Access .mdb files. Hoping for some help, people, on this. Especially if you know it has worked for you.Thanks Kristen.Regards,Lester VincentSydney |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-14 : 19:03:23
|
quote:
(a) empty and fill them, or(b) delete and re-import them from the Access .mdb files. Hoping for some help, people, on this. Especially if you know it has worked for you.
Either or both of these COULD be performed using DTS. BCP may be a better option but I am less familiar with thatsteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
Lester Vincent
Starting Member
22 Posts |
Posted - 2006-01-15 : 17:28:26
|
| Thanks Steve. I am thinking of creating a VBA function that loops through a table, listing all table names (maybe could include a flag to select/deselect table) then include an ADODB.Command to call a SPROC which either uses "DROP TABLE" or "TRUNCATE TABLE".(There are about 150 tables, but some are lookups with static data.)That would clean out the practise data, and the tables too, if "DROP TABLE" is used.Then I guess I would need to similarly call a SPROC that would do a DTS Import on each table in the list, to get the "Live" data back into SQL Server tables. Can DTS be called in this way?I'm not sure if it is possible to do such an import call (ie within a Stored Procedure, with table name and path to the .MDB file) on DTS.Have you tried anything like this before? I'm sure someone has had to do it before me. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-16 : 03:38:06
|
| If you wanted to do it this way - and there are probably better ways than using DTS - then you don't neccessarily need the VBA nor the SProcs. Truncate table can be done within a DTS package and you can set precedence so that if it is successful the import package can go ahead. If it's all combined into one package then the impact of the import will be lessened but it won't generally be as fast as BCP or a bulk insertIf you have a lot of data you can speed the process up by dropping indices (and constraints if it is decent data) and recreating them after the import.I've attempted a similar thing before using DTS but it was much less time critical for me. If time was that much of an issue I wouldn't have used DTS. In my case I had a problem with indices when the tables were linked back to Access. steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
Lester Vincent
Starting Member
22 Posts |
Posted - 2006-01-16 : 06:47:56
|
| Thanks for your reply, Steve. I have about a month to prepare for this, but when cutover time arrives, I will have to make it quick, or kick everyone out for at least half a day. (The sort of thing you would probably do on a Saturday, when everyone else is at home, watching the footy or something better!)If all else fails, I will simply re-import the tables one at a time via DTS. Incidentally, some of the tables have 50+ columns and one such table has 90,000 rows added in just the one year of activity.Cheers,Lester Vincent |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-23 : 04:14:21
|
| If you have access to some sort of test environment that will at least give you an idea how long the whole process will take.steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2006-01-25 : 14:40:51
|
| Lester,Deleting the data from the tables should be a piece of cake, however, you may want to consider using DTS to just make a copy of your database schema. Use DTS, and export your database to a new database. If this has not worked for you, or has caused errors, once you get to the "Specify Table Copy or Query step", choose "Copy objects and data between SQL server databases".At the "Select Objects to Copy" step, uncheck "Copy Data".Uncheck "Copy all objects" click the "Select Objects" button and click "Select All" then click "Check" then "OK"... You may be asking yourself "What?!? that doesn't make much sense" .. I have experienced cases where the "Copy all objects" does not create the objects in the correct order.. And, this has fixed that problem for me in the past. Wierd, ey? Anyways.. Uncheck "Use Default Options" and then Click "Options" .. uncheck everything EXCEPT "Copy Primary and Foreign Keys". Click OK. Click Next, Next. OK.Wah lah! I just followed that procedure and I have a brand new copy of my 5.6GB database .. and it only took 1 second to execute!! (Please let me know if that works on your end too)Ok, now that you have the new database... You will need to copy your data from the access database .. are you familiar with the OPENROWSET command? If not you need to be. It is invaluable when working with Access and SQL Server together! Are you familiar with linked servers? If not, you need to be. You can query an access database from SQL Server this way. (you can also use OPENROWSET) .. For example, you could query your historical data without importing the data into your SQL server database. Lastly, INSERT INTO myTable (a,b,c) SELECT (a,b,c,) FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) ..Thats a quick and dirty example.. not sure if the syntax is exact.. I'm trying to cover the basics. I would suggest linked servers, if you are going to be performing this operation more than once, e.g. in test, and then in production.. OPENROWSET may be more flexible in that you can just run the script on any server, and you would not have to create the linked servers first... (however you can script that too)I sincerely hope this helps.Rob |
|
|
|
|
|
|
|
|
Access to SQL - Startegy for Going Live With SQL