ACCESS FEED: Re: [MS_AccessPros] Re: Import Excel Spreadsheet into Access 2003 table

Here's what the code is doing.

1. Connect to database. I generally place it's full path into a named cell to allow for user managed changes when the database gets moved.
2. The code than pulls the records from the table to be updated to test the raw data to ensure that if already added, it won't be duplicated.
3. Assuming that the data is not duplicated, then edits are performed to either scrub the data of transform it to appropriate formats.
4. Once edited, valid data is added to the table opened earlier.
5. If invalid data is found, a number of possibilities can be chosen to notify the user. I generally write the record to a log worksheet but didn't do so in this example.
6. Finally, once all data has been processed, then cleanup is processed and the code declares victory.

Having been a mainframe DBA for years and for almost as many I've used and developed Access and other Windows based databases, it just offends my sensibilities to add "bad" data to a table, even a temporary table. That's why I shared the Excel based alternative. It won't allow invalid data to ever get to the database and this lessens the risk associated with unexpected results when reporting or using the data down the road.

An additional benefit to editing the data prior to updating the table is that the user can be immediately notified and "forced" to correct the errors. This can also prevent any table updates at all until the data is clean and this would also include the user's penchant for putting their scent of the data entry piece of the puzzle. I am, though, assuming that the user community was involved in defining the UI to ensure that the piece thay use is logically tied to the way they do their work. With this assumption in hand, it becomes the user's responsibility to input valid data. It generally takes just a few "you can't load your data" events to wake up those who just have to tailor the standard input template.

Jeff

PS. If you wish additional code to get database location or to write to the log worksheet, let me know. All the Excel stuff is simple, once understood. Until then, it can be a pain in the database. JJ

----- Original Message -----

Thanks for your help on this issue. I'm not sure what all your code is doing Jeff.

I think I will look into what John V said, however, people are always finding ways to bypass any error checking.

John F.

--- In MS_Access_Professionals@yahoogroups.com , Jeff Jones wrote:
>
> Another possibility could be to scrub or validate the data prior to adding it to a table. Here's an example of some DAO code that adds rows to an Access table from Excel.
>
> Sub ProcessCOS_Report()
>
> ' Import COS Report data into access table
> ' Connect to the database
>
>
> Dim strSports As String
> Dim strArthroplasty As String
> Dim strFracture As String
> Dim strDiagnosis As String
>
> Dim newCount As Long
> Dim i As Long
> Dim cn As New ADODB.Connection
> Dim rs As Recordset
>
> Dim sh As Worksheet
> Dim myData As String
> Set sh = ActiveWorkbook.ActiveSheet
> myData = ThisWorkbook.Path & "\WNH_III_Operative_Log_New_JJ.mdb"
>
> With cn
> .Provider = "Microsoft.ACE.OLEDB.12.0"
> .Properties("Data Source") = myData
> .Open
> End With
>
> Set rs = New ADODB.Recordset
>
> 'open record
> rs.Open "select * from [TestCaseLogs];", cn, adOpenKeyset, adLockOptimistic
>
> COS_Rpt.Activate
>
> 'get record count in excel
> newCount = Cells.Range("A65536").End(xlUp).Row
>
> If RowCount + 2 > newCount Then
> MsgBox "No data to be append to Access!"
> End If
>
> LastRow = ActiveSheet.UsedRange.Rows.Count
> For i = 2 To LastRow ' start from row 2 in the worksheet
> If Not IsEmpty(Range("R" & i).Value) Then
> strDiagnosis = Range("R" & i).Value
> If Not IsEmpty(Range("S" & i).Value) Then
> strDiagnosis = strDiagnosis & ", " & Range("S" & i).Value
> If Not IsEmpty(Range("T" & i).Value) Then
> strDiagnosis = strDiagnosis & ", " & Range("T" & i).Value
> If Not IsEmpty(Range("U" & i).Value) Then
> strDiagnosis = strDiagnosis & ", " & Range("U" & i).Value
> If Not IsEmpty(Range("V" & i).Value) Then
> strDiagnosis = strDiagnosis & ", " & Range("V" & i).Value
> End If
> End If
> End If
> End If
> End If
>
> 'INSERT INTO [Case Log Fields] ( [Case Number], [Patient Name], [Medical Record Number], [Date of Surgery], Diagnosis, [CPT Code], [Procedure Description], [Secondary CPT Code], [Secondary Procedure], CPT_Code_Three, Procedure_Description_Three, CPT_Code_Four, Procedure_Description_Four, [Sports Medicine Case], Fracture_Case, Arthroplasty_Case )
> 'SELECT [Case Log Fields].[Case Number], [Case Log Fields].[Patient Name], [Case Log Fields].[Medical Record Number], [Case Log Fields].[Date of Surgery], [Case Log Fields].Diagnosis, [Case Log Fields].[CPT Code], [Case Log Fields].[Procedure Description], [Case Log Fields].[Secondary CPT Code], [Case Log Fields].[Secondary Procedure], [Case Log Fields].CPT_Code_Three, [Case Log Fields].Procedure_Description_Three, [Case Log Fields].CPT_Code_Four, [Case Log Fields].Procedure_Description_Four, [Case Log Fields].[Sports Medicine Case], [Case Log Fields].Fracture_Case, [Case Log Fields].Arthroplasty_Case
> 'FROM [Case Log Fields];
>
> strSports = "True"
> strArthroplasty = "False"
> strFracture = "True"
>
> With rs
> .AddNew ' create a new record
> ' add values to each field in the record
> .Fields("Medical Record Number") = Range("A" & i).Value
> .Fields("Patient Name") = Range("B" & i).Value & ", " & Range("C" & i).Value
> .Fields("Date of Surgery") = Range("G" & i).Value
> .Fields("CPT Code") = Range("H" & i).Value
> .Fields("Secondary CPT Code") = Range("I" & i).Value
> .Fields("CPT_Code_Three") = Range("J" & i).Value
> .Fields("CPT_Code_Four") = Range("K" & i).Value
> .Fields("Diagnosis") = strDiagnosis
> .Fields("Sports Medicine Case") = strSports
> .Fields("Fracture_Case") = strArthroplasty
> .Fields("Arthroplasty_Case") = strFracture
> .Update ' stores the new record in the input_com_Copy table
> End With
> Next i
> DoEvents
>
> ' close the record set and connection
> rs.Close
> Set rs = Nothing
> Set dbs = Nothing
> cn.Close
> Set cn = Nothing
>
> Exit Sub
>
> HandleError:
> Stop
> ' MsgBox "Error Number: " & Err & " -- " & "Error: " & Error & " -- " & "Error Line: " & Erl
> 'MsgBox "Error Number: " & Err & " -- Error: " & Error & " -- Error Line: " & Erl & " -- Procedure: " & Application.VBE.ActiveCodePane.CodeModule & " -- Procedure: " & C_PROC_NAME
> 'MsgBox Err & " " & Error
> Debug.Print "MsgBox Err: " & Err & " Error: " & Error
>
> Resume 'Next
> End Sub ' end ProcessCOS_Report
>
> Thus endth my 02 cents,
> Jeff
> ----- Original Message -----
>
>
>
>
>
> John-
>
> An alternative would be to build an existing table with the correct field
> names, but all the data types set to Text. Import the data into that, then
> run code on the imported data that examines the data row by row and corrects
> it. You could even spit it out into another table with correct data types
> after massaging it.
>
> I'm not much of an expert on Excel, but it seems to me you could add code to
> the spreadsheet to validate the data as it's entered. Don't allow anything
> but a date in a date column, for example.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto: MS_Access_Professionals@yahoogroups.com ] On Behalf Of dnwinberry
> Sent: Wednesday, January 30, 2013 3:53 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Import Excel Spreadsheet into Access 2003 table
>
> John,
>
> I hope others chime in on this, but my idea would be to send the vendors a
> locked down database that they can update that contains a procedure to
> create the spreadsheet after they make the updates and emails it to your
> customer. You could use the free Access runtime so the vendors wouldn't have
> to have Access installed on their PC's.
>
> Doyce
>
> --- In MS_Access_Professionals@yahoogroups.com , "jfakes.rm" wrote:
> >
> > I have a customer that sends an Excel spreadsheet to different vendors for
> employee updates. I developed a spreadsheet template that is used to send
> to the vendor.
> >
> > The vendors are supposed to enter their data into the template using the
> specified data types. Then, my customer imports the data into a temp table
> then using a process I developed (using a form), the customer runs a series
> of steps to clean the data then add the data to the "main table."
> >
> > The problem is, the vendors are rearranging the layout of the template,
> entering text in date fields etc. So, when my customer wants to import the
> data into the temp table they get errors (sent to paste errors table). They
> then say my process doesn't work. When I sit with them and troubleshoot the
> issues on the spreadsheet, they exclaim that the automated process takes
> almost as much time to complete as it would to manually enter the data. I
> agreed that might be true on small vendors, but when you have to update 500+
> records, that would take a lot of time.
> >
> > Can anyone point me to a good source to learn how to automatically
> troubleshoot errors on the spreadsheet (i.e. ignore text in date fields
> etc.)? I told my customer to lock the formats on the spreadsheet however,
> the vendors continually make changes anyway.
> >
> > Any help would be appreciated.
> >
> > John F.
> >
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
> --
>
>
> Jeffrey Park Jones
> Excel, Access, Word, Office Expert
> Excel and Access, LLCÂ®
> http://ExcelAndAccess.Com
>
> 919-671-9870
> 5109 Deer Lake Trail
> Wake Forest, NC 27587
> jpjones23@...
>
>
>
>
> [Non-text portions of this message have been removed]
>

--

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

[Non-text portions of this message have been removed]