Send Data to Access
 

I have a spreadsheet with one row of information. The first field is a
Julian Date. I need to go to Access and if the Julian Date already exists
in the table called "Production" then update that record with the new info.
But if the Julian date is not in the table, I simply need to add the row as
a new record.

Thanks for any help!!

Send Data to Access
 

I have a spreadsheet with one row of information. The first field is a
Julian Date. I need to go to Access and if the Julian Date already exists
in the table called "Production" then update that record with the new
info.
But if the Julian date is not in the table, I simply need to add the row
as
a new record.

Something like the following should work.

HTH,
Merjet

Sub Macro1()
'Microsoft DAO x.x must be checked at menu Tools | References
Dim db As DAO.Database
Dim rs As Recordset
Dim bFound As Boolean

Set db = OpenDatabase("C:\Path\db1.mdb")
Set rs = db.OpenRecordset("Production")
bFound = False
Do Until rs.EOF Or bFound = True
rs.MoveNext
If Sheets("Sheet1").Range("A2") = rs("Day") Then
bFound = True
rs.Edit
rs("Sales") = Sheets("Sheet1").Range("B2")
rs.Update
End If
Loop
If bFound = False Then
With rs
.AddNew
!Day = c
!Sales = Sheets("Sheet1").Range("B2")
.Update
End With
End If
rs.close
Set rs = Nothing
db.close
Set db = Nothing
End Sub

Send Data to Access
 

Thanks!! It worked perfectly :)

"merjet" wrote in message
news:QFRlb.8811$e01.20360@attbi_s02...
I have a spreadsheet with one row of information. The first field is a
Julian Date. I need to go to Access and if the Julian Date already
exists
in the table called "Production" then update that record with the new
info.
But if the Julian date is not in the table, I simply need to add the row
as
a new record.

Something like the following should work.

HTH,
Merjet

Sub Macro1()
'Microsoft DAO x.x must be checked at menu Tools | References
Dim db As DAO.Database
Dim rs As Recordset
Dim bFound As Boolean

Set db = OpenDatabase("C:\Path\db1.mdb")
Set rs = db.OpenRecordset("Production")
bFound = False
Do Until rs.EOF Or bFound = True
rs.MoveNext
If Sheets("Sheet1").Range("A2") = rs("Day") Then
bFound = True
rs.Edit
rs("Sales") = Sheets("Sheet1").Range("B2")
rs.Update
End If
Loop
If bFound = False Then
With rs
.AddNew
!Day = c
!Sales = Sheets("Sheet1").Range("B2")
.Update
End With
End If
rs.close
Set rs = Nothing
db.close
Set db = Nothing
End Sub