Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database for checking out books | Excel Discussion (Misc queries) | |||
Checking status of issues based on first two digits of a field | Excel Discussion (Misc queries) | |||
Checking on first two digits of field | Excel Discussion (Misc queries) | |||
Can you Sparse a field in Excel??? and Database Query?? | Excel Discussion (Misc queries) | |||
Import from Database using field from excel. | Excel Discussion (Misc queries) |