Send Data to Access

[Tiffany]

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!!

 

[merjet]

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

 

[Tiffany]

Thanks!! It worked perfectly

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