Updating Microsoft access data to SQL server data

Posted on 2000-03-08
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.

Question by:felicia

Expert Comment

ID: 2595335
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>


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

Expert Comment

ID: 2595382
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!

LVL 43

Accepted Solution

TimCottee earned 200 total points
ID: 2595401
Here is a potential example, I have just knocked it out so there may be errors which you will have to sort out. It requires a reference to ADO in your project. The process is:

Read access table, go through records one by one, find equivalent record in searchindex, set updaterecord = 1, read table name, open this table at the same product no and update the values from the current access record. I have assumed that the productno field is character rather than numeric, if it is numeric then remove the single quote bits :- '.

Dim cnnAccess As ADODB.Connection
Dim cnnSQL As ADODB.Connection
Dim rstAccess As ADODB.Recordset
Dim rstSQL As ADODB.Recordset
Set cnnAccess = New ADODB.Connection
Set cnnSQL = New ADODB.Connection
cnnAccess.ConnectionString = "Insert Connection String For Access DB"
cnnSQL.ConnectionString = "Insert Connection String For SQL DB"
Set rstAccess = New ADODB.Recordset
rstAccess.Open "SELECT * FROM <AccessTableName>",cnnAccess,adOpenStatic,adLockOptimistic
With rstAccess
    rstSQL.Open "SELECT * FROM SearchIndex WHERE ProductNo = '" & rstAccess!ProductNo & "'",cnnSQL,adOpenStatic,adLockOptimistic
    rstSQL!UpdateRecord = 1
    strTableName = rstSQL!TableName
    rstSQL.Open "SELECT * FROM " & strTableName & " WHERE ProductNo = '" & rstAccess!ProductNo & "'",cnnSQL,adOpenStatic,adLockOptimistic
    rstSQL!DealerPrice = rstAccess!DealerPrice
    'and so on
  Loop Until .Eof
End With
IF rstSQL.State = adStateOpen Then rstSQL.Close
IF rstAccess.State = adStateOpen Then rstAccess.Close
Set rstSQL = Nothing
Set rstAccess = Nothing
Set cnnSQL = Nothing
Set cnnAccess = Nothing

Author Comment

ID: 2598613
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()
    Label1.Caption = adoaccess.Recordset.RecordCount
    Do While Not adoaccess.Recordset.EOF
        adosql.Recordset.Find "ProductNo='" & adoaccess.Recordset.Fields("ProductNo") & "'"
        If Not adosql.Recordset.EOF Then
            adosql.Recordset.Fields("updateRecord") = True
            adoaccess.Recordset.Fields("updateRecord") = True
        End If
End Sub

Thanks you very much.

LVL 43

Expert Comment

ID: 2599669
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.


Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question