Need some help from you Access gurus. 1

[BlackTL]

I am trying to see if this is possible. I have an excel spreadsheet with approximately 30 fields, and 3000 records. I also have an Access database with 21 fields (from the 30 in the spreadsheet. 9 were added to the spreadsheet) and approximately 2000 records (from various sources). Ok, now I want to compare the excel spreadsheet records with the access records and do several things...

(1) If the record exists in the spreadsheet and not the database, add the record to the database.
(2) If the record in the spreadsheet (which is more up to date) differs from the record in the Access Database, update the database with the new information.
(3) For the 9 new fields that are in the spreadsheet, I want to add them to the database and update each record in the database with the information in these 9 new fields.

If anyone can help me out as to what would be the most efficient way to do this, it would be a great help...

BTW, I'm using Access 97 and Excel 97

Thanks,
Rosh

 

[Tranman]

Rosh,
Just add the new columns to your Access table, then link the Excel spreadsheet as a table, then do like this:

Public Function AddOrUpdate()
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim intColPtr As Integer
Dim strLName As String
con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & CurrentDb.Name & ";"
rs1.Open "Select * from LinkedXL", con, adOpenDynamic, adLockOptimistic, adCmdText
rs1.MoveFirst

Do While Not rs1.EOF
'WE'RE ASSUMING CUST NUMBER IS A UNIQUE KEY
rs2.Open "Select * from Cust where CUST_NBR = " & rs1.Fields("CUST_NBR&quot & ";", con, adOpenDynamic, adLockOptimistic, adCmdText

Select Case rs2.EOF
Case True 'NEED TO ADD ROW
rs2.AddNew
For intColPtr = 0 To rs1.Fields.Count - 1
rs2.Fields(intColPtr) = rs1.Fields(intColPtr)
Next
Case False 'ROW EXISTS--NEEDS UPDATED
For intColPtr = 0 To rs1.Fields.Count - 1
rs2.Fields(intColPtr) = rs1.Fields(intColPtr)
Next
End Select

rs2.Update
rs2.Close
Set rs2 = Nothing
rs1.MoveNext
Loop

rs2.Close
Set rs2 = Nothing
con.Close
Set con = Nothing
End Function

You need to make **REALLY** sure your columns are in the same order in your table as they are in your linked spreadsheet in order to use column numbers instead of hard coding all of the column names. If you do that, then this code will work fine. You'll also need a reference to the Microsoft ActiveX Data Objects in your module.

Good Luck,
Tranman

 

[Tranman]

Rosh,
Just add the new columns to your Access table, then link the Excel spreadsheet as a table, then do like this:

Public Function AddOrUpdate()
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim intColPtr As Integer
Dim strLName As String
con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & CurrentDb.Name & ";"
rs1.Open "Select * from LinkedXL", con, adOpenDynamic, adLockOptimistic, adCmdText
rs1.MoveFirst

Do While Not rs1.EOF
'WE'RE ASSUMING CUST NUMBER IS A UNIQUE KEY
rs2.Open "Select * from Cust where CUST_NBR = " & rs1.Fields("CUST_NBR&quot & ";", con, adOpenDynamic, adLockOptimistic, adCmdText

Select Case rs2.EOF
Case True 'NEED TO ADD ROW
rs2.AddNew
For intColPtr = 0 To rs1.Fields.Count - 1
rs2.Fields(intColPtr) = rs1.Fields(intColPtr)
Next
Case False 'ROW EXISTS--NEEDS UPDATED
For intColPtr = 0 To rs1.Fields.Count - 1
rs2.Fields(intColPtr) = rs1.Fields(intColPtr)
Next
End Select

rs2.Update
rs2.Close
Set rs2 = Nothing
rs1.MoveNext
Loop

rs2.Close
Set rs2 = Nothing
con.Close
Set con = Nothing
End Function

You need to make **REALLY** sure your columns are in the same order in your table as they are in your linked spreadsheet in order to use column numbers instead of hard coding all of the column names. If you do that, then this type of code will work fine. You'll also need a reference to the Microsoft ActiveX Data Objects in your module.

Good Luck,
Tranman

 

[BlackTL]

Thanks a lot! Will this work even though the two unique ID's are not for the same record?

Eg. ID 1 = A
ID 2 = B
ID 3 = C for the first table

ID 1 = F
ID 2 = A
ID 3 = L for the second table

One way I wanted to do it was to use an account number which is not the primary key because there are some accounts without account numbers. I was thinking try and match the account numbers first, then for the ones with null acctNo's, use the account Name?

Thanks,
Rosh

 

[Tranman]

Rosh,
The way this works is that it 'visits' each row of the linked Excel table, one at a time, and attempts to find a corresponding row in the Access table. If it finds a corresponding row in the Access table, it updates it with **ALL** of the info from the Excel row(thus, replacing all info that matches, and also, all that doesn't match). If it doesn't find a corresponding row, it creates a new row in the Access table, and populates it from the current Excel row.

All of this implies that there is some way to identify the row in the Access table that 'matches' the current row in the linked Excel table. It could be that you don't have one field that is unique to both the linked Excel rows and the Access rows, and you have to use multiple rows in the rs2 query in order to determine for certain whether a matching row exists, or must be created.

Now, on to your questions...You ask if this will work even though "the two unique ID's are not for the same record?". I'm not sure what you're asking, but if you mean that the first row of the linked Excel table Id1=A, Id2=B, Id3=C, matches with the seventeenth row of the Access table, that is not a problem. We can just change the SQL in the rs2 open statement to say something like, "Select * from Cust where Id1= " & rs1.Fields("Id1&quot & " and Id2= " & rs1.Fields("Id2&quot & " and Id3= " & rs1.Fields("Id3&quot & ";". Here again, the only thing is, we need to be certain that we can identify the single row in the Access table that matches a single row in the linked Excel table(or be certain that no matching record exists). We can deal with whatever field(s) we need to use in order to do this.

As to your second question, using a field (or fields) that are not the primary key is not a problem. The main reason people prefer to use the primary key, is that you can be certain that it is unique. It doesn't sound like the account number would be a very likely candidate, but you might be able to dummy some account numbers into the rows that don't have them, then use the field....you know your data.

You might give some thought to what fields you can use to uniquely identify both the linked Excel rows, and the matching Access rows.

Write back when you feel like you know the answer, and I'll help you trick up the SQL to accomplish what you want. Hopefully I don't need to say that you should make a backup copy of both the Excel spreadsheet and the Access database BEFORE you make any changes.

I will stick with you until we get your problem resolved.

Tranman

 

[BlackTL]

Tranman, sorry I didn't get back to you earlier but I've been busy all day... Ok, so here's where I am at this point. I tried your code and I received errors I didn't even know what they meant. I should probably mention that I have no idea as to how to write code in Access or VB. I know some SQL queries but that's about it.

Anyway, I converted the spreadsheet to a .mdb database file and added the fields and renamed them so that they are identical to the original database.

So now I have two databases with 40 fields in order (many were added)

Now, I have to just

(1) Merge the two databases together and get rid of the duplicates.

 

[willir]

Folks

This code uses ADO. Access 97 supports DAO and not ADO. This probably explains the errors.

Coding would work, but update and append queries may also work. Perhaps if you dont want to dive into usng code, using queries may be a more suitable approach for you.

BUT, if you are a bit new to databases, you should probably spend a bit of time to make sure database is going to do what you want.

This may help...
thread700-628486

 

[Tranman]

Rosh,
Sorry, I was buried yesterday.

First, regarding Richard's admonishment about Access 97 not supporting ADO: Access 97 supports ADO just fine (I've been using it for years), but ADO is not the default data object library, so before you use the ADO, you have to add a reference to it to your project. Hence, the last line of my first post:

"You'll also need a reference to the Microsoft ActiveX Data Objects in your module."

I might have emphazized this comment more, but I made assumptions about your relative level of experience.

The way you add a reference to the ADO library to your project is to open the module where your code is, then go to Tools|References and scroll down until you find "Microsoft ActiveX Data Objects nn Library", and put a check in the box beside it then close the window.

Richard, I agree with you about using the simplest tool that does the job. Rosh's description of the problem implied to me that he needed a cursor in order to use one simple process to both append and update his table,

"If anyone can help me out as to what would be the most efficient way to do this...",

and you really can't do cursor processing in Access without getting into the code window.

Now, back to Rosh's problem. No matter if you use update and append queries, or code containing update and append logic, you are still going to need to identify a field or combination of fields in each row that makes that row of the first (Excel) table unique, **AND** matches something unique in the second table. If there is nothing in your data that does this, then you are not going to be able to merge these two tables, no matter what tool you use, and you need to revisit your table design before going any further.

I might consider joining the tables on address and phone number, or something else like that is **probably** unique, and inserting dummy account numbers into the rows that don't have them, in order to create a unique field.

The bottom line is that, once you have identified or created a unique field or combination of fields that is in all rows of both tables (what will be the primary key of your final table), we can use it in my original SQL for rs2, and life will be good.

Tranman

 

[willir]

Tranman

Thanks for the correction. I learn something every day. A star for you.

Richard

 

[Tranman]

Richard,
Thanks for the star. The real beauty of this forum is the sharing of our experiences. I, too, learn something new every time I visit here.

I started using ADO exclusively in Access 97, back at the point when I got my beta copy of Access 2000 and saw the direction they were going. We never did go to 2000 at work, but this spring we converted all of our Access 97 databases to Access XP, and boy, was I glad that I had used the ADO all this time.

BTW, I learned a couple of things from your post in the thread you referenced in your first post....back at you.

Paul (Tranman)

 

[willir]

Tranman

I went back and looked and my older Access books and not one mentions ADO, and as you know Access 2000+ really pushes ADO.

But man, it is more complex in some ways such as the connect string -- more flexible, but more complex. And authors of the Access books I have read seem to want / are hoping that Microsoft continues to support DAO. Example: Access Database, Design and Programming (pub: O'Reilly, Auth: Roman)

In change, there is profit for some and pain for others -- more books to write, more code to change.

Richard

Richard