Returning number of tables in SQL Server2000 using ADO in VB6

I'm using a VB6 application to access data from an SQL Server 2000 database. There are a few things I would like to do:

1. Get the number of tables in the database
2. Automatically begin reading data from each table in the database. Is it possible to write some code that will cause the application to start on first table retrieve all the data, then move to next table (similar to moving to next row)?

Thanks,
JoeyIt depends on what version of ADO you are using. However, there is a function called NextRecordset. Honestly I can't imagine why you would ever want to pull so much information at once though?

hth!

Originally posted by joey55
I'm using a VB6 application to access data from an SQL Server 2000 database. There are a few things I would like to do:

1. Get the number of tables in the database
2. Automatically begin reading data from each table in the database. Is it possible to write some code that will cause the application to start on first table retrieve all the data, then move to next table (similar to moving to next row)?

Thanks,
Joey|||Unfortunately I have been assigned to create a database comparison tool in VB6 to test data integrity between original and upgraded databases. How does NextRecordset work? Is it a member of the connection object?

I want to be able to loop through all the tables in the database from first to last withought having to specify them, cause we have thousands of tables.|||It is part of the recordset object. What is the purpose of retrieving all the data from the table - what will you do with it ?|||I will compare it with the data in the same table retrieved from the upgraded database. This needs to be done for each table. and there are thousands of them.

I'm not sure if NextRecord can help me, you have to Open a recordset to one table as far as I know. I need an object outside of that that can return the name of all the tables.|||So are you looking for missing records, differences in column data ... ? Just describe what happened that provoked this comparison. Are you trying to sync the 2 up. Is the existing database (not the upgraded one) considered the master or is the upgraded considered the master - or is it somewhere in between ?|||Ok here is the scenario...

We just released our latest version of our software. Many clients of previous release versions want to upgrade to the newest version. In the new version some changes have been made to the database, so the DBA wrote some scripts to upgrade client databases to the newest version. This is where I come in. We want to make sure that NO data is lost when the upgrade scripts are run. There are thousands of tables and probably hundreds of thousands of records so it is impossible to be manually done.

So I have been assigned the task of creating a tool that will go through each table in both the original and the upgraded database and compare the exising data, to make sure nothing is lost or changed by fault in the upgrade scripts.

My plan is (unless someone suggests something better) to write some code that will start on the first table in the original database, find same table in upgraded one, and make sure all the data is identical in both and if it differs will right the problem to a log file.

What I wamt to know is if there is a way to return all the tables (like a recordset would rows). Maybe from the sysobjects table or something. I'm not used to working with SQL Server 2000 from the development point of view. All suggestions and help welcome.

Thanks,
Joey