Updating Microsoft access data to SQL server data
Posted on 2000-03-08
I had a Microsoft Access 97 data table call template sited at local drive with the following fields:
ProductNo, DealerPrice, updateRecord
I had SQL Data table call searchIndex sited at SQL server with the following fields:
ProductNo, TableName, updateRecord
My program is to read access recordset ProductNo and than read SQL recordset ProductNo.
When SQL recordset ProductNo equal to Access recordset ProductNo, the SQL updateRecord will set to 1.
This means that the product need to update its DearPrice and ListPrice from the access table. But this update is not to be done on the searchIndex SQL table.
The things is the program need to read SQL searchIndex field TableName. TableName refer to company name and contain ProductNo, DealerPrice and ListPrice. There are about 1000 records of company name in SQL server.
Here the part that I don't know how to approach using visual basic.
I create two ADO object, one is to read SQL data and one for Access data, and a command click object to update the data.
this section it work that the program update the updateRecord field to 1.
Hope this is not confuse.
The whole process is:
Read Access data, read SQL data, compare the ProductNo, update the updateRecord field. If updateRecord field is 1, read TableName.
for example, the TableName APC is a set of table data with fields such as ProductNo, DealerPrice, ListPrice.
How then can I using ADO in visual baisc to go to the company name after reading the TableName recordset at SQL searchIndex table.
As ADO only access to 1 database record file...if am right, then I had problem access to the company table.
Is there a way to solve this problem?
Many Thanks in advance.