June 30, 2008 at 9:38 am
Hi brainy people..
I have a database of 300 plus tables that was developed over a period of about 10 years by my predecessor. I want to import the data into SQL, because the Access database is just unwieldy. I've used the upsizing wizerd to import the data into SQL, but there's a fair amount of integrety problems with the data, for example, in the access database there are date columns that fall outside of SQL's date/time range because someone made a typo and typed 984 instead of 1984.
Now this wouldn't be such a problem, except when the upsizing wizard runs into an issue, it fails to import ANY data at all.. So I'm left with 56 tables with no data in them, where as the Access Database contains data, with at least one table with more than a million rows :w00t:
What I would like is to write a query in SQL that can scan the tables and report back the rowcount, so I can ensure that there is an equal number of rows in Access and SQL, like there should be. I have the query drawing data from sysobjects and sysindexes, giving me a rowcount in SQL, but I've no idea how to get this data from an Access linked server except by individually running "select count(*) from {linkedservername}...{tablename}" on each table individually.
Can anyone suggest an easier way of doing this? Pretty Please:D
June 30, 2008 at 9:44 am
If you go into the tables tab in Access, it has an option for showing hidden/system tables. The data you need should be in those. I don't remember the exact details, but I think you can do that (the last time I used an actual Access database was 2001, so I'm a little rusty on the subject).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply