Search -Add-Update Data to Access
 

I thought I had worked out a solution to my coding and correcting a
Looping problems, but it has turned out to be a much greater problem.

In our office we have multiple staff members needing to Update and/or
Add data to an Access Database from Excel, that is because none of
these staff members have direct access to the database, only through
Excel.

I can access the database, and the proper table and Add data to the
Table from the Excel Macro. However, it will add everytime. So if
staff add rows later, and push the macro they end up added the same
rows over and over. I want the rows which match existing data in the
access table to be updated if necessary, and add if not found.

Coding is right for the connection to the Database and recordsets.

I attempted to use the following :

Dim rs As Recordset
Dim ColE As Varient
........
For R = 4 to 300
ColE = Worksheets("Contacts").Cells(R,5).Value
If ColE = "" Then
Exit For
End If
Do While Not rs.EOF
If Worksheets("Contacts").Cells(R,5).Value = rs.Fields("SSN") Then
rs.Edit
rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Value
' etc.etc. (total of 12 fields of data
rs.Update
Else
rs.MoveNext
rs.AddNew
rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Vales
' etc. etc.
rs. Update
End If
Loop
Next
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing

I can see the problem in that I am looping through the Excel
spreadsheet okay, but failing to loop through the Database Table???
No sure how to do this?

Thanks for any help.

Search -Add-Update Data to Access
 

In your Access app:

1. Open the Table in design view
2. Add an index to the table to prevent duplicate records.

This is done using the "Indexed" property at the bottom of the screen. It
would also help if you had a primary key defined in the table.

P


"Pete T" wrote in message
om...
I thought I had worked out a solution to my coding and correcting a
Looping problems, but it has turned out to be a much greater problem.

In our office we have multiple staff members needing to Update and/or
Add data to an Access Database from Excel, that is because none of
these staff members have direct access to the database, only through
Excel.

I can access the database, and the proper table and Add data to the
Table from the Excel Macro. However, it will add everytime. So if
staff add rows later, and push the macro they end up added the same
rows over and over. I want the rows which match existing data in the
access table to be updated if necessary, and add if not found.

Coding is right for the connection to the Database and recordsets.

I attempted to use the following :

Dim rs As Recordset
Dim ColE As Varient
.......
For R = 4 to 300
ColE = Worksheets("Contacts").Cells(R,5).Value
If ColE = "" Then
Exit For
End If
Do While Not rs.EOF
If Worksheets("Contacts").Cells(R,5).Value = rs.Fields("SSN") Then
rs.Edit
rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Value
' etc.etc. (total of 12 fields of data
rs.Update
Else
rs.MoveNext
rs.AddNew
rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Vales
' etc. etc.
rs. Update
End If
Loop
Next
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing

I can see the problem in that I am looping through the Excel
spreadsheet okay, but failing to loop through the Database Table???
No sure how to do this?

Thanks for any help.

Search -Add-Update Data to Access
 

Phobos' suggestion to add a primary key to the Access table
would help, but looping the recordset is still needed. Try the
following:

Dim rs As Recordset
Dim ColE As Variant
Dim bFound As Boolean

. . . . .
For R = 4 To 300
ColE = Worksheets("Contacts").Cells(R, 5).Value
If ColE = "" Then Exit For
bFound = False
rs.MoveFirst
Do
If Worksheets("Contacts").Cells(R, 5).Value = rs.Fields("SSN") Then
bFound = True
rs.Edit
rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Value
' etc.
rs.Update
End If
rs.MoveNext
Loop Until rs.EOF Or bFound = True
If bFound = False Then
rs.AddNew
rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Vales
' etc.
rs.Update
End If
Next R
rs.Close
Set rs = Nothing
dbs.Close

HTH,
Merjet

Search -Add-Update Data to Access
 

Merjet you are the Master... Thanks with some minor adjustment of my
If..Then statement it work bueatifully. Now I need to complicate the
If..Then with a double search as we will have multiple users and I
need to narrow the search area. Each staff member who will be adding
and updating records here has a unique login. The login resides at one
location on the Spreadsheet (F1), If I attempt to use the following
If..Then :

If rs.Fields("Login")=Range("F"&1).Value And _
ColE = rs.Fields(SSN") Then

It doesn't work, as the first login value is i.e. "6701" and the F1
Range is 6701 . without quotes. So everything fails to match and the
code ends doing nothing?

I have heard that there is a way to first create a temporary query
sorting out all other Login's then run the If...Then on the SSN only,
do you know how?

Thanks Again,

"merjet" wrote in message .net...
Phobos' suggestion to add a primary key to the Access table
would help, but looping the recordset is still needed. Try the
following:

Dim rs As Recordset
Dim ColE As Variant
Dim bFound As Boolean

. . . . .
For R = 4 To 300
ColE = Worksheets("Contacts").Cells(R, 5).Value
If ColE = "" Then Exit For
bFound = False
rs.MoveFirst
Do
If Worksheets("Contacts").Cells(R, 5).Value = rs.Fields("SSN") Then
bFound = True
rs.Edit
rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Value
' etc.
rs.Update
End If
rs.MoveNext
Loop Until rs.EOF Or bFound = True
If bFound = False Then
rs.AddNew
rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Vales
' etc.
rs.Update
End If
Next R
rs.Close
Set rs = Nothing
dbs.Close

HTH,
Merjet

Search -Add-Update Data to Access
 

How about using
If rs.Fields("Login")=Range("F"&1).Text And _
ColE = rs.Fields(SSN") Then

the text property should return a text string instead of the actual number
value
Paul D

"Pete T" wrote in message
m...
Merjet you are the Master... Thanks with some minor adjustment of my
If..Then statement it work bueatifully. Now I need to complicate the
If..Then with a double search as we will have multiple users and I
need to narrow the search area. Each staff member who will be adding
and updating records here has a unique login. The login resides at one
location on the Spreadsheet (F1), If I attempt to use the following
If..Then :

If rs.Fields("Login")=Range("F"&1).Value And _
ColE = rs.Fields(SSN") Then

It doesn't work, as the first login value is i.e. "6701" and the F1
Range is 6701 . without quotes. So everything fails to match and the
code ends doing nothing?

I have heard that there is a way to first create a temporary query
sorting out all other Login's then run the If...Then on the SSN only,
do you know how?

Thanks Again,

"merjet" wrote in message
.net...
Phobos' suggestion to add a primary key to the Access table
would help, but looping the recordset is still needed. Try the
following:

Dim rs As Recordset
Dim ColE As Variant
Dim bFound As Boolean

. . . . .
For R = 4 To 300
ColE = Worksheets("Contacts").Cells(R, 5).Value
If ColE = "" Then Exit For
bFound = False
rs.MoveFirst
Do
If Worksheets("Contacts").Cells(R, 5).Value = rs.Fields("SSN")
Then
bFound = True
rs.Edit
rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Value
' etc.
rs.Update
End If
rs.MoveNext
Loop Until rs.EOF Or bFound = True
If bFound = False Then
rs.AddNew
rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Vales
' etc.
rs.Update
End If
Next R
rs.Close
Set rs = Nothing
dbs.Close

HTH,
Merjet