Hi folks - I am in need of help please
I am trying to de-duplicate data coming from Excel to Access
The part I am having problems with is
(I have attached the full code at the bottom too to help)
Any help would be greatly appreciated and needed - Set DBz = _
-
OpenDatabase("C:\Documents and Settings\_XXXXX_\My Documents\S_B\7-16-A_D_B.mdb")
-
Set rsz = DBz.OpenRecordset("Data_Weekly", dbOpenTable)
-
-
-
With ThisWorkbook.Worksheets("Data_Weekly")
-
ExcelRecord = Advocatecomp & MDate
-
End With
-
-
AccessRecord = rsz.Fields("Value") & rsz.Fields("Date")
-
-
If ExcelRecord = AccessRecord Then
-
bFound = True
-
Call MsgBox("Advocate Work Completed on time Metrics already exist in the ADB" _
-
& vbCrLf & "Please click ok to cancel this import" _
-
, vbCritical, "LLF- Ca")
-
-
Exit Sub
-
-
Else
-
bFound = False
-
Call MsgBox("Advocate Work Completed on time Metrics Do Not Already exist in the ADB" _
-
& vbCrLf & "Please click ok to import this metric" _
-
, vbCritical, "LLF- Ca")
-
End If
- Public Sub SaveWPPercent()
-
Dim MDate As Date
-
Dim Rptpath As String
-
Dim RptName As String
-
Dim DateCheck As Date
-
Dim Advocatecomp As Single
-
Dim X As Single
-
-
Dim MSQL As String
-
Dim DBS As DAO.Database
-
Dim RST As DAO.Recordset
-
Dim DBSName As String
-
Dim dBSPath As String
-
Dim Mmetric_ID As Single
-
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
Dim bFound As Boolean 'new 7-15
-
Dim ExcelRecord As String 'new 7-15
-
Dim AccessRecord As String 'new 7-15
-
Dim DBz As Database, rsz As DAO.Recordset, r As Long 'new 7-15
-
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
'open the report
-
'check to make sure the date matches
-
'find the metric ID in the ADB
-
'save the value to the weekly data table
-
'if the value is already there, then replace it
-
-
'make sure there is a date in the date box----------------------------------------------------------------------------------------
-
If Not IsDate(Me.cboWE.Value) Then
-
MsgBox "Please select a week ending date!", vbCritical, "Error"
-
Exit Sub
-
Else
-
MDate = Me.cboWE.Value
-
End If
-
-
'make sure report there is a report chosen----------------------------------------------------------------------------------------
-
If Me.txtWorkPackage = "" Then
-
MsgBox "Please enter a valid report path for Advocate Completed on Time!", vbCritical, "Error"
-
Exit Sub
-
Else
-
Rptpath = Me.txtWorkPackage
-
End If
-
-
'check to see if the dates match--------------------------------------------------------------------------------------------------
-
Workbooks.Open Rptpath, False, True
-
RptName = ActiveWorkbook.Name
-
DateCheck = Workbooks(RptName).Worksheets("Input Sheet").Cells(2, 1)
-
If DateCheck <> MDate Then
-
If MsgBox("The date in the workbook: " & RptName & " do not match! Do you wish to continue?", vbYesNo, "Error! Dates do not match!") = vbYes Then
-
MsgBox "The value will be assigned to the weekending date of " & MDate & "!"
-
Else
-
Exit Sub
-
End If
-
-
End If
-
-
'find the Advocate completed on time metric for Italy--------------------------------------------------------------------------------
-
For X = 1 To 25
-
If Workbooks(RptName).Worksheets("Input Sheet").Cells(X, 3).Value = "Italy total (calc=1)" Then
-
'we have found the row
-
Advocatecomp = Workbooks(RptName).Worksheets("Input Sheet").Cells(X, 5).Value
-
Workbooks(RptName).Close False
-
Exit For
-
End If
-
-
Next X
-
-
'If there is no data then --------------------------------------------------------------------------------------------------------
-
If Advocatecomp = 0 Then
-
MsgBox "Unable to locate the Italy Advocate completed on time value!", vbCritical, "Error!"
-
Exit Sub
-
End If
-
-
'SQL to set data -----------------------------------------------------------------------------------------------------------------
-
MSQL = " SELECT Metrics.Metric, Reporting_Hierarchy.Level_1, Metrics_X_Reporting_Hierarchy.Metric_ID, Data_Weekly.Date, " _
-
& "Data_Weekly.Value " _
-
& "FROM ((Metrics_X_Reporting_Hierarchy INNER JOIN Metrics ON Metrics_X_Reporting_Hierarchy.Metric_Name_ID = Metrics.Metric_Name_ID) " _
-
& "INNER JOIN Reporting_Hierarchy ON Metrics_X_Reporting_Hierarchy.Hierarchy_ID = Reporting_Hierarchy.Hierarchy_ID) " _
-
& "INNER JOIN Data_Weekly ON Metrics_X_Reporting_Hierarchy.Metric_ID = Data_weekly.Metric_ID " _
-
& "WHERE (((Metrics.Metric)='" & "Advocate Completed On Time - Weekly" & "') " _
-
& "AND ((Reporting_Hierarchy.Level_1)='" & "Italy" & "') " _
-
& "AND ((Data_weekly.Date)='" & MDate & "'));"
-
-
'set the variable-----------------------------------------------------------------------------------------------------------------
-
bFound = False 'added 7-16
-
-
'where is the path and name of the access file -----------------------------------------------------------------------------------
-
dBSPath = "C:\Documents and Settings\ra94\My Documents\Scorecard_Button"
-
DBSName = "7-16-MOS_Data_Repository.mdb"
-
-
'set DBS--------------------------------------------------------------------------------------------------------------------------
-
Set DBS = OpenDatabase(dBSPath & "\" & DBSName)
-
-
'set record set-------------------------------------------------------------------------------------------------------------------
-
Set RST = DBS.OpenRecordset(MSQL)
-
If Not RST.EOF Then
-
'record exists, find the record in the data_montly table and edit the value of the existing record
-
'add to restatement and notify the user
-
-
Mmetric_ID = RST!metric_ID
-
Set RST = Nothing 'want to reuse the variable - need to clear it out.
-
MSQL = "SELECT Data_weekly.Metric_ID, Data_weekly.Date, Data_weekly.Value " _
-
& "FROM Data_weekly " _
-
& "WHERE (((Data_weekly.Metric_ID)= " & Mmetric_ID & ") " _
-
& "AND ((Data_weekly.Date)='" & MDate & "'));"
-
Set RST = DBS.OpenRecordset(MSQL)
-
RST.MoveFirst
-
RST.Edit
-
RST!Value = Advocatecomp
-
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
Set DBz = _
-
OpenDatabase("C:\Documents and Settings\_XXXXX_\My Documents\S_B\7-16-A_D_B.mdb")
-
Set rsz = DBz.OpenRecordset("Data_Weekly", dbOpenTable)
-
-
-
With ThisWorkbook.Worksheets("Data_Weekly")
-
ExcelRecord = Advocatecomp & MDate
-
End With
-
-
AccessRecord = rsz.Fields("Value") & rsz.Fields("Date")
-
-
If ExcelRecord = AccessRecord Then
-
bFound = True
-
Call MsgBox("Advocate Work Completed on time Metrics already exist in the ADB" _
-
& vbCrLf & "Please click ok to cancel this import" _
-
, vbCritical, "LLF- Ca")
-
-
Exit Sub
-
-
Else
-
bFound = False
-
Call MsgBox("Advocate Work Completed on time Metrics Do Not Already exist in the ADB" _
-
& vbCrLf & "Please click ok to import this metric" _
-
, vbCritical, "LLF- Ca")
-
End If
-
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
RST.Update
-
Set RST = Nothing
-
-
-
Else
-
'record doesn't exist. Find the metric_ID in the CR table - if the metric id is found, insert the record into the data_monthly table
-
-
MSQL = "SELECT Reporting_Hierarchy.Level_1, Metrics.Metric, Metrics_X_Reporting_Hierarchy.Metric_ID " _
-
& "FROM (Reporting_Hierarchy INNER JOIN Metrics_X_Reporting_Hierarchy ON Reporting_Hierarchy.Hierarchy_ID = Metrics_X_Reporting_Hierarchy.Hierarchy_ID) " _
-
& "INNER JOIN Metrics ON Metrics_X_Reporting_Hierarchy.Metric_Name_ID = Metrics.Metric_Name_ID " _
-
& "WHERE (((Reporting_Hierarchy.Level_1)= '" & "Italy" & "') " _
-
& "AND ((Metrics.Metric)= '" & "Advocate Completed On Time - Weekly" & "'));"
-
-
-
Set RST = DBS.OpenRecordset(MSQL)
-
RST.MoveFirst
-
Mmetric_ID = RST!metric_ID
-
Set RST = Nothing
-
MSQL = "Select * from Data_Weekly"
-
Set RST = DBS.OpenRecordset(MSQL)
-
RST.AddNew
-
RST!Date = MDate
-
RST!metric_ID = Mmetric_ID
-
RST!Value = Advocatecomp
-
RST!Status = "Active"
-
RST.Update
-
Set RST = Nothing
-
MsgBox "Metrics have been imported!", vbOKOnly, "Import Completed!" 'moved from import click to here
-
End If
-
-
-
End Sub
-
13 4737 nico5038 3,080
Recognized Expert Specialist
Just curious why the user has to click for ignoring duplicates.
I would have excluded them entirely from the data set to be inspected, or ignored them and issued an INSERT that will skip dupes for the specified unique key field(s).
What's the way you intend this code to work?
Nic;o)
Hi Nico thanks for getting to my thread I could really use your help. All I need is to make sure the data being imported via a command button if pressed more then once that the data does not duplicate as many as extra times the button was pressed. So lets say you press that button to import metrics into access. But another co-worker did not check access to see if the data is there, and they press the button to import the data again. I need to block this from happening. Please advise
nico5038 3,080
Recognized Expert Specialist
As indicated, when inserting a new record, Access will "block" duplicates when you have defined the unique ID.
I see your add code:
RST!Date = MDate
RST!metric_ID = Mmetric_ID
RST!Value = Advocatecomp
RST!Status = "Active"
and I assume MDate, Mmetric_ID and Advocatecomp make up the unique record. Thus defining a unique index on the table consisting from these fields with the option "No duplicates allowed" will make sure no duplicate records can be added.
I normally issue a "Docmd.exec ute ("<Insert statement>")" to add rows and this will suppress error messages when adding a duplicate row, as it won't add the data.
Still would need some information on the "Status" field. When there's a row with Status <> "Active", will this trigger the Status field to change ?
Nic;o)
Nico again thank you so much. You are a bit over my head - could you simplify things or please give the code to exit sub if found?
nico5038 3,080
Recognized Expert Specialist
The whole point is that I don't check for duplicates, I just add all rows and Access will "drop" the duplicates because of the defined unique key in the table.
Normally I import an excel sheet into a temp table and show that to the user in a form. Here I add an indication showing duplicates yes or no. (using an outer (left or right) join with the production table.)
Thus the user can see which rows will be added and check for typo's causing erroneous duplicates. After correcting this the final import can be executed "straight away" with an "append" query, no code needed.
What is the reason to show the rows first to the user, instead of importing it directly?
Nic;o)
Nico - I think I am getting it now.
Can you show me a code where this will take my data and do your method with a form? I know I am asking a lot but I would really appreciate it
nico5038 3,080
Recognized Expert Specialist
Just attach (part of) the .mdb and a sample excelsheet to your post (Use "Go Advanced" button) so I can show you.
(I'm lazy, I know :-)
Nic;o)
great will do when I get the file after the weekend.
May I ask why is this not working? - Private Sub CommandButton1_Click()
-
On Error GoTo errline
-
-
-
' 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=C:\Documents and Settings\Ben\My Documents\Excel VBA\7-18.mdb;"
-
' open a recordset
-
Set rs = New ADODB.Recordset
-
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
-
' all records in a table
-
r = 2 ' 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("date") = Range("A" & r).Value
-
.Fields("item") = Range("B" & r).Value
-
.Fields("number") = Range("C" & r).Value
-
' add more fields if necessary...
-
.Update ' stores the new record
-
-
-
End With
-
r = r + 1 ' next row
-
Loop
-
exitline:
-
Exit Sub
-
-
errline:
-
Select Case Error.Number
-
Case 2147217887
-
MsgBox "This would cause duplicates in the MDR click OK to cancel"
-
Cancel = True
-
-
Case Else
-
MsgBox "There was an error in the program please contact MOS Administrator"
-
GoTo exitline
-
End Select
-
rs.Close
-
Set rs = Nothing
-
cn.Close
-
Set cn = Nothing
-
-
-
End Sub
nico5038 3,080
Recognized Expert Specialist
Hmm, the Range looks a bit odd, as it's an excel method and I would have expected automation instead of recordset processing. Once a sheet has been opened as a recordset, the origin (text, excel, access, etc.) doesn't bother.
In general I just use linked excel sheets to get all handling similar.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Marc Ederis |
last post by:
Hello,
I'm having a problem with sending data with sockets over a dial-up
connection. When I use the send function, it will happily send a
buffer of a megabyte and more in one shot. But of course, the data is
still in the network buffer... Meaning you can't disconnect for awhile
(but for how long...). The problem is, how can I know when it's done?
Is there a way to be notified when the data has truly been sent?
I tried using...
|
by: sridevi |
last post by:
Hello
How to export data from ms-access database to excel worksheet using ASP.
mainly i need to export data to multiple worksheets. it is very urgent to
us.
i have a sample code which works only exporting to single worksheet. but i
need to export data to multiple worksheets.
it is very urgent to us. so please help me in code.
|
by: Paolo |
last post by:
Friends,
I need help with some code to export different tables to a single
spreadsheet in Excel.
My excel file is named REPORT and the spreadsheet is named CLIENTS.
I do have the code to export a single table to Excel but have problems
with multimple tables.
Thanks.
|
by: PrinStation |
last post by:
I am looking to import data from Excel to Access. The data is set up in one
Excel file, but on 12 (monthly) sheets. After importing the first month/sheet
(which I did successfully), I was looking to use the old dBase 3 plus command
"append" to bring in the eleven remaining sheets. I am thus far unable to find
"append" or rather its Access equivalant. What to do? Any help is appreciated.
|
by: Martin |
last post by:
There is an Access table on the network. 15 users who do not have Access are
connected to the network. Is there a way for each user to be able to enter one
or more rows containing 3 or 4 columns to Excel on his machine and then press a
button or something and append that data to the Access table on the network? Any
suggestions on what the code would be?
Thank you very much!
Martin
| |
by: Steve Jorgensen |
last post by:
Hi folks,
I'm posting this message because it's an issue I come up against relatively
often, but I can't find any writings on the subject, and I haven't been able
to figure out even what key words one would use to look for it.
First, in broad philosophical terms, code actually -is- data. Code is the
data that's fed into a compiler, interpreter, or microprocessor that tells it
what to do. Code execution is, then, just a form another...
|
by: tkaleb |
last post by:
I have to create output file in a text, MS Access, MS Excel and .dbf format
from C# Win/ADO.NET application. Data are collected in DataSet and there is
no problem to make text file. However, I have to create a new output files
(tables with defined fields) in other 3 formats, and to fill them with data
from DataSet. I created Excel output with ComponentOne's trial Excel
component, but it is a temporary solution. Also I have unusual CP (1250...
|
by: nuti |
last post by:
Hi all,
I am fairly new to VB.I am trying to figure out as how to write a
script so that i can read the data from an excel sheet to Access.
can u guys please help me out?
cheers,
nuti
|
by: =?Utf-8?B?YzY3NjIyOA==?= |
last post by:
Hi all,
I have a question for you.
I have a .csv file which has many lines of data.
Each line has many data fields which are delimited by ",".
Now I need to extract part of data from this file but save it as an excel
file.
The data in this excel file will be imported into an Access database. The
|
by: dkriese |
last post by:
I am using Quickbooks reporting tool that can export an xls or csv file. The data being exported needs to create a classroom scheduler. The data being exported is from a sales receipt via QB and contains date and time, etc.
Question is how can I get that row of data imported into access to create a calendar type schedule for those days and for the month. I need to see all the students scheduled for the each of the dates, the class...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |