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

Jumat, 01 Februari 2013

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

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]
>