from Excel to Access

[buddyel]

Does anyone know how I can modify the following code so that before saving a new record, it will test for the existence of the text entered in Range("B8&quot in FIELD8 in the Access database, and if it does exist already, prompt the user to overwrite the record. Any suggestions are greatly appreciated..

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\engdata.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblEngdata", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew 'creates a new record
'add values to each field in the record
.Fields("field8&quot = Range("b8&quot.Value
.Fields("field9&quot = Range("b9&quot.Value
.Fields("field10&quot = Range("b10&quot.Value
.Fields("field11&quot = Range("b11&quot.Value
.Fields("field12&quot = Range("b12&quot.Value
.Fields("field13&quot = Range("b13&quot.Value
.Fields("field14&quot = Range("b14&quot.Value
.Fields("field15&quot = Range("b15&quot.Value
.Fields("field16&quot = Range("b16&quot.Value
.Fields("field17&quot = Range("b17&quot.Value
.Fields("field18&quot = Range("b18&quot.Value
.Fields("field19&quot = Range("b19&quot.Value
.Fields("field20&quot = Range("b20&quot.Value
.Fields("field21&quot = Range("b21&quot.Value
.Fields("field22&quot = Range("b22&quot.Value
.Fields("field23&quot = Range("b23&quot.Value
.Fields("field24&quot = Range("b24&quot.Value
.Fields("field25&quot = Range("b25&quot.Value
.Fields("field26&quot = Range("b26&quot.Value
.Fields("field27&quot = Range("b27&quot.Value
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[kevinclark]

Hi buddyel

I am assuming that Field8 is a unique value else if duplicates are allowed, and if some exist - which record do you overwrite.


So this code checks for existance - If exists already then prompts user to overwrite or cancel. If not exist already then addnew.


Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

Dim ExistingRecord As Boolean
Dim prmptUser As Long

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Development\Database\engdata.mdb"
' open a recordset
Set rs = New ADODB.Recordset


rs.Open "Select * from tblEngdata where [Field8] = '" & Range("B8&quot.Value & "'", cn, 3, 3

With rs
If .RecordCount > 0 Then
ExistingRecord = True
Else
ExistingRecord = False
End If
If ExistingRecord = True Then
prmptUser = MsgBox("Record already exists - Overwrite Record ?", vbYesNo + vbQuestion, "What to do ?&quot
If prmptUser = vbYes Then
'Overwrite
For lngPos = 0 To 1
.Fields(lngPos).Value = "FIVE"
Next lngPos
.Update
Else
'Cancel
Exit Sub
End If
Else
.AddNew 'creates a new record
For lngPos = 8 To 27
.Fields(lngRow).Value = Cells(lngRow, 2).Value
Next lngPos
.Update
End If
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[kevinclark]

Try this one instead - I had some code written in the "Overwrite" section of the last post that would allow me to test against a database on my PC. This post has it corrected to what you would want.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

Dim ExistingRecord As Boolean
Dim prmptUser As Long
Dim lngPos As Long

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Development\Database\engdata.mdb"
' open a recordset
Set rs = New ADODB.Recordset


rs.Open "Select * from tblEngdata where [Field8] = '" & Range("B8&quot.Value & "'", cn, 3, 3

With rs
If .RecordCount > 0 Then
ExistingRecord = True
Else
ExistingRecord = False
End If
If ExistingRecord = True Then
prmptUser = MsgBox("Record already exists - Overwrite Record ?", vbYesNo + vbQuestion, "What to do ?&quot
If prmptUser = vbYes Then
'Overwrite
For lngPos = 8 To 27
.Fields(lngRow).Value = Cells(lngRow, 2).Value
Next lngPos
.Update
Else
'Cancel
Exit Sub
End If
Else
.AddNew 'creates a new record
For lngPos = 8 To 27
.Fields(lngRow).Value = Cells(lngRow, 2).Value
Next lngPos
.Update
End If
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[buddyel]

I now would like to add some values from column C. The cells are scattered (c5, c7, c14, c15, c44). How do i modify your for..next statement to add these in....
The fields will probably be named (fieldc5,fieldc7, etc..)

For lngPos = 8 To 27
.Fields(lngRow).Value = Cells(lngRow, 2).Value
Next lngPos

 

[kevinclark]

Hi Buddyel

The For .. Next statement in the last post worked out only because there was a correaltion between the cell rows and the fields and they all incremented by 1. If the cells are scattered in column C as you mentioned and there is no set increment then you may have to write them out for Column C as similar to what you had in your original post.

I am not sure if this will help but just a note about the For .. Next Loop

You can adjust the way it increments as follows

For lngRow = 2 to 10 Step 2
Msgbox LngRow
Next lngRow

The above code would return 2, 4, 6, 8 and then 10.