471,310 Members | 1,486 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,310 software developers and data experts.

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

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
2 11871
NeoPa
32,404 Expert Mod 16PB
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
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.

Similar topics

5 posts views Thread by Johnny Meredith | last post: by
6 posts views Thread by David Gartrell | last post: by
4 posts views Thread by Earl Anderson | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.