We help IT Professionals succeed at work.

Updating Microsoft access data to SQL server data

felicia
felicia asked
on
215 Views
Last Modified: 2010-08-05
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.


Comment
Watch Question

wqw

Commented:
absolutely impossible.... :-)) just kidding

well, ado is reading as much record files (???columns???) as you want this way:

Dim rs as New Recordset
rs.Open "Select * from searchIndex", <YourConnectStringToSQL>

</wqw>

p.s. i give up. this is too basic. find a book on SQL

Commented:
If I understand correct:

The Access table is your backbone for your application: If the user does anything, the updateRecord field is set to 1 to indicate that the 'master' database needs to be updated.

Then you look in the indexSearch SQL for the comparing ProductNo, and if found collect the TableName.

Then in the table TABLENAME (????) you have to look for that productno and update it.

It this correct ? So you have different table names which contain ProductNo's ?
Are u using SQL Server (7) ?

Let me know!

D'Mzzl!
RoverM
Applications and Integrations Consultan
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Many Thanks TimCottee's.

However, my code was different from my approach.

Can u give comments about my approach.

Attached for your reference.

Private Sub cmdUpdatPrice_Click()
    adosql.Refresh
    adoaccess.Refresh
    'adoaccess.Recordset.RecordCount
    Label1.Caption = adoaccess.Recordset.RecordCount
     
   
    Do While Not adoaccess.Recordset.EOF
        adosql.Recordset.MoveFirst
        adosql.Recordset.Find "ProductNo='" & adoaccess.Recordset.Fields("ProductNo") & "'"
       
        If Not adosql.Recordset.EOF Then
             
            adosql.Recordset.Fields("updateRecord") = True
            adoaccess.Recordset.Fields("updateRecord") = True
            adosql.Recordset.Update
            adoaccess.Recordset.Update
                               
           
        End If
        adoaccess.Recordset.MoveNext
        Loop
     
End Sub


Thanks you very much.

TimCotteeApplications and Integrations Consultan

Commented:
Your approach is not really that different, the major difference is that you have opened the entire SQL table and used Find to get the appropriate record whereas I have opened the sql table on each row with only the matching row in the recordset, I can't really say which is better, if the SQL table is small then using VB to do the find may not be a problem, however if the SQL table is large, using find which is at the client level may be slower than opening the recordset each time with one record as it is then up to the SQL server to do the work not the vb client.

But neither is wrong.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.