By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Import Data into MS Access

P: 4
This will require some VBA code to sequentially process the records.
The H and D types indicate to me that you have a master (order?) record and a child (OrderDetails) record that will belong to the master.

You'll need code like:

Expand|Select|Wrap|Line Numbers
  1. ' define recordsets for master and detail table
  2. dim rsMaster as DAO.recordset 'This requires Tools/References to hold a Microsoft DAO version #.## reference
  3. dim rsDetail as DAO.recordset
  4.  
  5. dim strLine as string
  6. dim strKey as string
  7.  
  8. set rsMaster = currentdb.openrecordset("tblMaster")
  9. set rsDetail = currentdb.openrecordset("tblDetail")
  10.  
  11. open "C:\input\myfile.txt" for input as #1
  12. while not eof(1)
  13.   input #1, strLine
  14.   if len(strLine) > 1 then
  15.   select case left(strLine,1)   
  16.    case "H"
  17.      strKey = mid(strLine,2,10)
  18.      rsMaster.addnew
  19.      rsMaster!Keyfield = mid(strLine,2,10)
  20.      rsMaster!Field1 = mid(strLine,12,10)
  21.      '... etc
  22.      rsMaster.Update
  23.    case "D"
  24.      rsDetail.addnew
  25.      rsDetail!Keyfield = strKey
  26.      rsDetail!Field1 = mid(strLine,12,8)
  27.      rsDetail!Field2 = mid(strLine,20,5)
  28.      '... etc
  29.       rsDetail.Update
  30.    end select
  31.   endif
  32. wend
  33.  
  34.  
It's "aircode", but I hope it gives the idea how to process the data.
Each row not starting with H or D will be ignored and the H will define the key for the D.

Nic;o)
I have question similar to the above:
I want a code that will import text, excel, mdb files from a specified drive in to an existing or new Access table. Any suggestions will be appeciated.
Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdStartImport_Click()
  2.  
  3. Dim rst As ADODB.Recordset
  4. Dim strNewTable As String
  5. Dim strFileType As String
  6.  
  7.  
  8.  
  9. On Error GoTo ErrorHandler
  10.  
  11. 'Check if the file name was entered'
  12. If IsNull(Me.selFileName) Then
  13. MsgBox "You must enter the path and file name.", vbEclamation, "Error"
  14. 'Me.File.SetFocus
  15. 'GoTo StartImportBtn_Exit
  16. End If
  17.  
  18.  
  19. 'If new table was checked, verify tablename entered
  20. If Me.chkNewTable = True And IsNull(Me.txtTableName) Then
  21. MsgBox "You must enter the new file name.", vbExlamation, "Error"
  22. Me.txtTableName.SetFocus
  23.  
  24. End If
  25.  
  26. 'Determine what information to import
  27. Select Case Me.txtPath
  28.   Case 1
  29.   strExistingTableName = "tbl_Name_0122"
  30.   Case 2
  31.   strExistingTableName = " tbl_Name1"
  32. Case Else
  33.   MsgBox "You must enter the new file name.” vbExlamation, "Error"
  34. Me.txtPath.SetFocus
  35. End Select
  36. 'if new table, then set the variable for the new table name
  37.   If Me.chkNewTable = True Then
  38.     strNewTableName = Me.TableName
  39. Else
  40. strNewTableName = "tbl_Temp"
  41. On Error Resume Next
  42. DoCmd.DeleteObject acTable, "tbl_Temp"
  43. On Error GoTo ErrorHandler
  44.  
  45.  
  46. End If
  47.  
  48. 'Determine the type of file being imported by determining the last file extension"
  49.  
  50. strFileType = Right$(Me.selFileName, 3)
  51.  
  52. Select Case strFileType
  53.  
  54.  Case "mdb" 'Access Database
  55.   If Me.chkNewTable = True Then
  56. DoCmd.TransferDatabase acImport, "Microsoft Access", Me.selFileName, acTable, strExistingTableName, strNewTableName
  57.  
  58.  
  59. Case "txt" ', "csv" ' Check for text files
  60.   DoCmd.TransferText acImport, acImportDelim, strNewTableName, Me.selFileName, True
  61.   Case Else
  62.   MsgBox "Please enter a correct file type, choose *.mdb, *.xls, *.txt or *.csv.", vbExlamation, "Error"
  63.  
  64.   GoTo Done
  65.   End Select
  66.   ‘If new table was selected, then done, else append data to existing table
  67.   If Me.chkNewTable = False Then
  68.  
  69.   ' Run append query from temp table to new table
  70.   DoCmd.RunSQL "INSERT INTO" & _
  71.   strExistingTableName & _
  72.   "SELECT tblTemp" & _
  73.   "FROM tbl_Temp"
  74.   DoCmd.DeleteObject acTable, "tbl_Temp"
  75.   End If
  76.  
  77.   'Display the Import is Completed"
  78. MsgBox "Import Successfully Completed.", vbExclamation, "Import Status"
  79. GoTo Done
  80.  
  81.  
  82.  
  83. Error Handler:
  84. MsgBox Err.Description
Feb 21 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Firstly I've split this into a new thread as it was not directly related to the other thread so was essentially a hijack.
As to your question :
If you look in (POSTING GUIDELINES: Please read carefully before posting to a forum) you should see what is necessary to include in a question. I'm sorry but this is not a simple code fixing service. We can respond to specific questions and we have many very helpful and friendly experts who will happily do that for you.
You need to ask a straightforward question for that though. If your code isn't working for you then you need to say exactly what you are looking for and specify what error message you are receiving and whereabouts in the code.
That's a bare minimum.

MODERATOR.
Feb 22 '07 #2

P: 4
Firstly I've split this into a new thread as it was not directly related to the other thread so was essentially a hijack.
As to your question :
If you look in (POSTING GUIDELINES: Please read carefully before posting to a forum) you should see what is necessary to include in a question. I'm sorry but this is not a simple code fixing service. We can respond to specific questions and we have many very helpful and friendly experts who will happily do that for you.
You need to ask a straightforward question for that though. If your code isn't working for you then you need to say exactly what you are looking for and specify what error message you are receiving and whereabouts in the code.
That's a bare minimum.

MODERATOR.
Thanks for your advice> As I mentioned in my introductory statement, I posted this question before I saw the guidelines.
Feb 23 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
Now I'm thoroughly confused.
There's still no attempt to rephrase the question (in this thread) so I'll lock it unless and until I hear from you via PM that you would like to continue with it and are ready to post a question that follows the guidelines (I'll assume that you've now read them - clearly my last assumption after directing you to them in an earlier thread was premature).
Maybe the introductory statement made things clearer but I was unable to find anything that fits your description.

MODERATOR.
Feb 25 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.