July 28, 2011 at 3:36 pm
What's that?
July 28, 2011 at 3:48 pm
SSMA is a very good tool provided free from MS that will convert an Access (Oracle, MySQL, etc) database to SQL Server.
For Access, it converts data types, creates triggers, indices, etc. for all tables
Then, on request, it converts many of your queries to T-SQL and loads them into your DB,
Then, it loads the actual data from the Access DB into SQL Server.
Then, on request, it creates pass-through queries for all the tables in your db, renaming the native tables so they're still available.
Of course, it has many shortcomings. It can't find SQL embedded in your Access forms, reports, etc.
It won't convert UNION queries
It won't convert INSERT queries
It can't deal with Crosstab queries
And a few more items, but it does seem to work very well for me, particularly since my BE db was fully normalized and well-indexed with few "awkward" column names.
Google SSMA. Again, it's free!
Jim
August 2, 2011 at 10:09 am
Well, I've fixed the problem. I set up the form to run the (Access) query, creating a "temp" table (residing on SQL Server), then, I wrote a brief SP to truncate the target table and load up the target from the temp table. Sub-second for the SP. The Access query runs in maybe 3 minutes now (I've done some streamlining.) Four lines of Access code.
I'm still working on converting the Access query to SS. It's largely done, but since the SS tables are not up to the minute, it's still moot.
I also learned how to rerun just the migrate step of a SSMA project. Works great. It re-migrates all the tables from Access to SS2k5 in maybe 6 minutes. Now, I just need to figure out how to automate that without using the UI so I can schedule it every hour or so....
Ninja, thanks for the encouragement and help!
Jim
August 2, 2011 at 10:25 am
err. So how do you host the data??? The data should ONLY be on SQL server, nothing in access. That way it's always up to the second!
August 2, 2011 at 10:29 am
And why do you truncate the table? That won't work multi-users!
August 2, 2011 at 10:36 am
I am mid-conversion. I have over a hundred forms to test before I can convert. There is no halfway conversion. Users continue to use the Access BE until I can finish testing the forms. If I convert the users and it fails, recovery will be unpleasant, so I'm taking it very slowly.
This particular table has already been moved to SS, but its source data is still in Access.
You say truncate won't work multi-user. Remembering that this table is read-only for everyone else except the one user doing the refresh, will it do anything other than require a read-only user to "refresh" his/her local recordset when the refreshed table is in place?
Jim
August 2, 2011 at 10:41 am
Multi-users permanent temp tables only work if you use a filter (@@SPID).
Other than that you'll always end up with conflit and bad data.
August 2, 2011 at 12:36 pm
The front end is in Access. Access opens up a DAO recordset on the SQL Server data. If rows are deleted, it sees they were deleted and notifies the user (who, as I said, is using it read-only.) The user then needs only hit <shift>F9 and Access will requery the datasource. All good.
Jim
August 2, 2011 at 12:43 pm
This may be a bit late to the party, but - the easiest way to reload a table is to front it with a view. This way you can be running with 2 shadow tables: one holding the data being presented right now, and a scratch one you can reload into.
This way - you reload the data, and then alter the view.
For example if your view were
create view myView as select * from tableA
you could reload to TableB,and then issue
alter view myView as select * from tableB
and voila - no fuss no muss.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 2, 2011 at 12:48 pm
... and you do that how in a multi-user env?
August 2, 2011 at 1:04 pm
If everyone is using the view as the basis for their work (be it a report or an Access "table"), change the definition of the view, and everyone points at the new source. Same effect as deleting the records, just without the lag. It may take a second to get the lock on the view, but you'd run into the same issue with deleting everything.
In other words - none of the apps would be talking to the actual base table. they would be querying the view only.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 2, 2011 at 1:13 pm
I'm probably not being very clear.... Lemme try.
Construction Project Accounting System (CPAS...)
Front end...A2007, no tables, just forms, reports, queries. All table references linked.
Back end...Split
A2007 -- all the transaction and reference tables (~100).
SS2k5 -- a couple of summary tables used read-only by a half dozen Access 2007 forms.
The SS2k5 summary tables are build by summarizing and flattening a dozen transaction and reference tables in Access, and Inserted into the SS2k5 tables. I keep them in their own SS2k5 DB.
I'm converting the system (an average of 3 admin users and 3-5 management users) so all the tables will be on SS2k5. But since I have around a hundred separate forms and reports, built over a 5-year learning curve with Access (2003 and 2007), and half a dozen import functions (Excel, text, etc....)
I've run the conversion (using SSMA) 35 times now, and all the tables convert smoothly now. 85% of the queries convert smoothly, and the rest are action queries that can stay in Access for now. The forms often have internal SQL datasources that need attention, and sometimes use queries that are updateable in Access, but not updateable as passthrough queries. So, I'm slogging through all that now.
Jim
August 2, 2011 at 1:13 pm
I'll let him beat his head into the wall for a while.
Bottom is that unless it's a DW, centralized table with drop/load don't work for multi-users unless you use @@spid.
Great day!
August 2, 2011 at 1:16 pm
Interesting... "change the definition..." Do you suggest I do that in the view definition on SS2k5, or the connection string in Access (not really practical, since every user has his or her own copy of the FE....)
Jim
August 2, 2011 at 1:17 pm
OK, that answered my question. I'll try it.
Jim
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply