Automate import into multiple Access tables

[mcchung52]

Hi everyone,
I need to import a flat database (excel) of 10 columns to an existing Access database, linked with relationships, though contains no data
so far. It was initially set up with Excel but now needs to be ported
to a safer and more stable platform. It now contains about 70k rows.
I think I need to import it row by row because as I'm importing I need to
drop the data into its corresponding table then get the id then
enter some of remaining data into another table and do that couple times
into another table.
I looked into doing this with queries or series of queries but couldn't
figure it out. So should I do this with VBA? or are there another ways?


Any help would be appreciated. Thanks.

[ssanfu]

Hi everyone,
I need to import a flat database (excel) of 10 columns to an existing Access database, linked with relationships, though contains no data
so far. It was initially set up with Excel but now needs to be ported
to a safer and more stable platform. It now contains about 70k rows.
I think I need to import it row by row because as I'm importing I need to
drop the data into its corresponding table then get the id then
enter some of remaining data into another table and do that couple times
into another table.
I looked into doing this with queries or series of queries but couldn't
figure it out. So should I do this with VBA? or are there another ways?
Any help would be appreciated. Thanks.

I would do the import with VBA, mainly because of error handling. Are there NULLs in any of the columns? You will need to get the PK from a table to insert into a related table as a FK.

You can manually link to the spreadsheet, open a record set and loop through the records, inserting the data into the tables.

Or you could import the spreadsheet into a temp table, then do the processing.

[mcchung52]

Yes there could be some NULLs and I actually need to perform
some checks on data because user directly entered into worksheet.
Could you show me some example code to get started?
Especially the PK part and latter part of your explanation...
What's hard for me is the part where I "need to get the PK from a table
to insert into a related table as a FK". So I guess I need to do this multiple
times?? then do I have to manually run the check whether everything went ok?

[mcchung52]

OK I did some searching on Internet and came up with following code to
open excel connection and access database connection.

Dim db As Database
Dim rs As DAO.Recordset
Dim sql As String

Dim appExcel As Object 'Excel.Application
Dim wbk As Object 'Excel.Workbook
Dim wks As Object 'Excel.Worksheet
Dim rng As Object 'Excel.Range

Dim fileName As String

Dim fDialog As Office.FileDialog 'added reference to library Office 12.0

' Set up the File Dialog. '
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
'GetOpenFilename like in Excel doesn't work in Access!!!

With fDialog
.AllowMultiSelect = False 'True ' Allow user to make multiple selections in dialog box '
.Title = "Please select one or more files" ' Set the title of the dialog box. '
.Filters.Clear ' Clear out the current filters, and add our own.'
.Filters.Add "Excel files", "*.xls"
.Filters.Add "CSV files", "*.csv"
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the '
' user picked at least one file. If the .Show method returns '
' False, the user clicked Cancel. '
If .Show = True Then

fileName = .SelectedItems.Item(1)
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

Set appExcel = VBA.CreateObject("Excel.Application")
appExcel.Visible = True

Set wbk = appExcel.Workbooks.Open(fileName)
Set wks = wbk.Worksheets(1)

'Open connection to current Access database
Set db = CurrentDb()
sql = "select * from 2007" 'test
Set rs = db.OpenRecordset(sql) 'test

Here I need to set up a for loop to loop thru the worksheet
(Ok I can do that) and check data and figure out where to
put what data into what table. Do I need to have multiple
connections up to each table in the Access file, like how I
set it up right above? or is there a cleaner and/or better way?

[ssanfu]

It is a good start.

Here is a site that helped me a lot when I was importing data from Excel spreadsheets:

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

'GetOpenFilename like in Excel doesn't work in Access!!!

Yes it does, but you need additional code:
http://access.mvps.org/access/api/api0001.htm


I haven't used the Application.FileDialog(msoFileDialogFilePicker) method, I always used GetOpenFilename.

Can't find the code I used yet. I had two loops: one for the row and one for the columns.
Here is the basic idea:

'my tables all have an autonumber as the PK

   RW = 5   'first row with data in spreadsheet
   numCols = 10  'number of columns of data


'show row number - control on form
   Me.Progress = RW
   Do Until Len(Trim(Cells(RW, 1))) = 0   ' stop when the first column of the row is empty

      ' clear variables
      FRate = 0
      BaseHrs = 0
      A_Fee = 0
      CPDID = 0

      Me.Repaint

      If RW Mod 50 = 0 Then     ' update every 50 rows.
         Me.Progress = RW
         OpenForms = DoEvents    ' Yield to operating system.
         Me.Repaint
      End If

'read all columns into variables
      For i = 1 To numCols   ' loop thru the columns
         Select Case i

            Case 1  '????   A

            Case 2  'Employee Number      B
               strEID = Trim(Cells(RW, i))     'read the data from the Excel cell

            Case 3  'Employee SSN     C

               strSSN = Trim(Cells(RW, i))     'read the data from the Excel cell
               strSSN = Replace(str, "-", "")
               'check len
               If Len(str) <> 9 Then
                  'handle error here
                  NumErrors = NumErrors + 1
               End If
            Case 4

            Case Else

         End Select
      Next i   'columns

      ' after all columns read, & processed,  insert into recordsets
'main table (record set)
      rs.AddNew
      rs!field1 = variable1
      .
      .
      .
      rs.Update

      'get PK
      MyPK = rs!MyID

      ' add data to other tables
      sSQL = "Insert Into ...."
      CurrentDb.Execute sSQL, dbFailOnError

      ' inc row number
      RW = RW + 1

   Loop  ' rows

   ' Do cleanup
   rs.close
   Set rs = nothing
   'tell me
   MsgBox "Done"

[mcchung52]

thank you.