Checking database field
 

i have a spreadsheet with a macro that uses DAO to append data to an
Access database.

See code below:
------
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access
database
' this procedure must be edited before use
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=P:\monthly200304.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Activity", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ReportingMonth") = Range("A" & r).Value
.Fields("ReportingOrg") = Range("B" & r).Value
.Fields("SubOrg") = Range("C" & r).Value
.Fields("Speci") = Range("D" & r).Value
.Fields("LineID") = Range("E" & r).Value
.Fields("LineDescription") = Range("F" & r).Value
.Fields("Month") = Range("G" & r).Value
.Fields("Value") = Range("H" & r).Value
.Fields("CommProv") = Range("I" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
-----

Can anyone let me know if it is possible to add some code at the start
of the macro to look at the database and only export data from excel
to access if there is a certain value in the "ReportingMonth" field of
the database. (Basically to prevent duplicate additions to the
database).

Thanks,
Chris

Checking database field
 

Chris

I think this check should be done in your Access database,
not through the vba code module. You need to set a primary
key based on 'ReportingMonth' in the database table. This
will then automatically prevent duplicate records being
created with no need for a check in your vba code.

hth

-----Original Message-----
i have a spreadsheet with a macro that uses DAO to append
data to an
Access database.

See code below:
------
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an
Access
database
' this procedure must be edited before use
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=P:\monthly200304.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Activity", cn, adOpenKeyset,
adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ReportingMonth") = Range("A" &
r).Value
.Fields("ReportingOrg") = Range("B" & r).Value
.Fields("SubOrg") = Range("C" & r).Value
.Fields("Speci") = Range("D" & r).Value
.Fields("LineID") = Range("E" & r).Value
.Fields("LineDescription") = Range("F" &
r).Value
.Fields("Month") = Range("G" & r).Value
.Fields("Value") = Range("H" & r).Value
.Fields("CommProv") = Range("I" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
-----

Can anyone let me know if it is possible to add some code
at the start
of the macro to look at the database and only export data
from excel
to access if there is a certain value in
the "ReportingMonth" field of
the database. (Basically to prevent duplicate additions
to the
database).

Thanks,
Chris
.

Checking database field
 

thanks tony, a good suggestion but i'd like the user to prompted if
likely to be adding duplicate entries.

For example if the database has some month 7 data in, and the user is
trying to add some more month 7 data, i'd like a message to pop-up
asking if the user is sure that they want to continue and add extra
data to the database.

is this possible??

chris


"TonyM" wrote in message ...
Chris

I think this check should be done in your Access database,
not through the vba code module. You need to set a primary
key based on 'ReportingMonth' in the database table. This
will then automatically prevent duplicate records being
created with no need for a check in your vba code.

hth

-----Original Message-----
i have a spreadsheet with a macro that uses DAO to append
data to an
Access database.

See code below:
------
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an
Access
database
' this procedure must be edited before use
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=P:\monthly200304.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Activity", cn, adOpenKeyset,
adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ReportingMonth") = Range("A" &
r).Value
.Fields("ReportingOrg") = Range("B" & r).Value
.Fields("SubOrg") = Range("C" & r).Value
.Fields("Speci") = Range("D" & r).Value
.Fields("LineID") = Range("E" & r).Value
.Fields("LineDescription") = Range("F" &
r).Value
.Fields("Month") = Range("G" & r).Value
.Fields("Value") = Range("H" & r).Value
.Fields("CommProv") = Range("I" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
-----

Can anyone let me know if it is possible to add some code
at the start
of the macro to look at the database and only export data
from excel
to access if there is a certain value in
the "ReportingMonth" field of
the database. (Basically to prevent duplicate additions
to the
database).

Thanks,
Chris
.