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

How do I programmatically import text, excel and mdb files in to an existing Access

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


NeoPa
Expert Mod 15k+
P: 31,186
I've warned you once already about asking a reasonable question (Import Data into MS Access). You've simply copied and pasted the same nonsense into this thread.
If you can't follow the posting guidelines then please don't expect anyone to feel obliged to respond to your question.

MODERATOR.
Feb 23 '07 #2

P: 4
I've warned you once already about asking a reasonable question (Import Data into MS Access). You've simply copied and pasted the same nonsense into this thread.
If you can't follow the posting guidelines then please don't expect anyone to feel obliged to respond to your question.

MODERATOR.
Moderator, with all due respect to you, I think the tone of your expression is very unethical and disrespectful. If you saw the times of the posting, you would realize that both were simultaneously posted. As I said in my previous rejoinder to your advice that I did not see the rules prior to posting my problems. When I googled your site, the first things I saw were questions posted by your members.By using the term" nonesense" is highly un professional and rude.
Feb 23 '07 #3

Post your reply

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