Results 1 to 6 of 6

Thread: Importing data

  1. #1
    Join Date
    Jun 2012
    Posts
    85

    Unanswered: Importing data

    Hey, I have a form with 9 text boxs:
    Address Information
    Portfolio
    Strategy
    Risk Management
    Service Providers
    Supporting Documents
    Desicion
    Monthly Data
    Daily Data

    Now I have a piece of code to display a promt box which lets me grab files from my computer. This is it:

    Code:
    Private Sub Command0_Click()
    
    On Error GoTo Import_Err
      
    Dim strFilter As String
    Dim strInputFileName As String
      
      strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.CSV)", "*.CSV")
      strInputFileName = ahtCommonFileOpenSave( _
                    Filter:=strFilter, OpenFile:=True, _
                    DialogTitle:="Please select an input file...", _
                    Flags:=ahtOFN_HIDEREADONLY)
     'Actual import
      If Len(strInputFileName) > 0 Then
        Me.AddInfo.value = strInputFileName
        
      End If
      
    Import_Exit:
      Exit Sub
      
    Import_Err:
      MsgBox Error$
      Resume Import_Exit
    End Sub
    How could I write this to run a string through the 9 text box names, and fill the correct text box based on the Button i click to prompt this. I want to make it a function so i can just call it from each command click rather then have the same code written over and over again.

    Heres my form template:

    Address Info ................txtBox........... CmdButton(0)
    Portfolio Sec ...............txtBox........... CmdButton(1)
    Strategy Sec ................txtBox........... CmdButton(2)
    Risk Management.........txtBox........... CmdButton(3)
    Service Providers .........txtBox........... CmdButton(4)
    Supporting Documents.txtBox........... CmdButton(10)
    Desicion ......................txtBox........... CmdButton(11)
    Monthly Data................txtBox........... CmdButton(5)
    Daily Data.....................txtBox........... CmdButton(12)

    Each text box fills in the file path at which i select from the promt menu.

    At the bottom of my form I have a button called import. when i click that button i want it to import all the files selected in the txt boxes.

    Code:
    If Len(strInputFileName) > 0 Then
        DoCmd.TransferText acImportDelim, "Import Specification", "tbl_Import", strInputFileName, False, ""
        
      End If
    this doesnt work for me, I thought it could. How would I write this code to import everything (append) into the corresponding table. Reminder the txt boxes titles correspond with the tables. The CSV are in exact same format as tables IE Field names.

    Thanks In advance, really appreciate the help here.
    Mike
    ________

  2. #2
    Join Date
    Jun 2012
    Posts
    85
    Ive changed some things up. makes it more tidy etc.

    I have 9 buttons on my form. all have this on click command: (I call the function)
    eg. [Call Import]
    Code:
     Function Import()
     On Error GoTo Import_Err
       
     Dim strFilter As String
     Dim strInputFileName As String
       
       strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.CSV)", "*.CSV")
       strInputFileName = ahtCommonFileOpenSave( _
                     Filter:=strFilter, OpenFile:=True, _
                     DialogTitle:="Please select an input file...", _
                     Flags:=ahtOFN_HIDEREADONLY)
      'Actual import
       If Len(strInputFileName) > 0 Then
         DoCmd.TransferText acImportDelim, "Import Specification", "tbl_Import", strInputFileName, False, ""
         
       End If
       
     Import_Exit:
       Exit Function
       
     Import_Err:
       MsgBox Error$
       Resume Import_Exit
       
     End Function
    When this code runs i want it to import to append it to the table. each button corresponds with its table. if someone could show me how to write this, that would be fantastic!
    it runs error, "The text file specification 'Import Specification' Does not exist. You cannot import, export, or link using the specification"

    is there a way to run the append to table....tblname here..

    Ie. Me.(Name of button?)=tblename, Append?
    (The bolded selection)

    thanks,
    Mike
    ________

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Instead of having 9 separate text boxes, why don't you simply make a combo box, with the 9 different data sources being rows, and the corresponding file names and table names being columns in the combo box? That way you could select the data source, and click on a command button. It's OnClick event would be a VBA procedure which would import the filename in Me.cbDS.Column(1) into table name Me.cbDS.Column(2). Column(0), of course would be the only column visible to the user, and contains the data source (the Text), such as "Decision" or "Portfolio," etc.

    If I'm over your head, read up on combo boxes in the Help file. It's a great resource! In fact, I suggest reading up even before using the combobox creation wizard, so you can use the wizard and get through it all in one try. What I'm suggesting is standard, by the way, not extra-special.

    Unless you have a valid Import Specification, don't put it in the statement. The Import Specification is an object, not a file name, and it doesn't exist unless you make it exist, and then it's saved under a different name. Just acknowledge the missed parameter with a comma.

    Sam

  4. #4
    Join Date
    Jun 2012
    Posts
    85
    im very familiar with combo boxes, so ill give er a shot!

    What your saying is to have a button which runs the open file... then import it to the table i select in the combo box? do i have to specify appending it or will it just append it?

    Unless you have a valid Import Specification, don't put it in the statement. The Import Specification is an object, not a file name, and it doesn't exist unless you make it exist, and then it's saved under a different name. Just acknowledge the missed parameter with a comma.
    do you mean to do this?

    Code:
    If Len(strInputFileName) > 0 Then
         DoCmd.TransferText acImportDelim, "Import Specification", ,(comma just before added)"tbl_Import", strInputFileName, False, ""
    And i am new to using code, I switched from macros so still learning to place things etc. where would i put the code to refrence the contents in the combo box? Im going to use a combo box which finds tables in my database.

    Code:
    Private Function GetTables()
    
    Dim Myarray As Variant
    Dim TablesSchema As ADODB.Recordset
    Dim conn As ADODB.Connection
    
    'reset cursorlocation to allow sorting in ordinal_position
       Set conn = CurrentProject.Connection
       With conn
       .CursorLocation = adUseClient
       End With
       
    'Get all database tables.
     Set TablesSchema = conn.OpenSchema(adSchemaTables)
     TablesSchema.Sort = ("TABLE_NAME")
     
     Me.cboTablesList.RowSource() = ""
     Do While Not TablesSchema.EOF
     
     'Exclude System Tables and Default Treeview Table
     If Left(TablesSchema("TABLE_NAME"), 4) = "MSYS" Or TablesSchema("TABLE_NAME") = "TREEVIEWFEED" Or Left(TablesSchema("TABLE_NAME"), 1) = "~" Then
     GoTo SKIP
     End If
     
            'Add Tables to the Combobox
            Myarray = Me.cboTablesList.RowSource()
            If Me.cboTablesList.ListCount < 1 Then
            Me.cboTablesList.RowSource = TablesSchema("TABLE_NAME")
            Else
            Me.cboTablesList.RowSource = Myarray & ";" & TablesSchema("TABLE_NAME")
            End If
    SKIP:
     TablesSchema.MoveNext
     Loop
    
    
    Set TablesSchema = Nothing
    End Function
    Mike
    ________

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Mike02 View Post
    im very familiar with combo boxes, so ill give er a shot!

    What your saying is to have a button which runs the open file... then import it to the table i select in the combo box? do i have to specify appending it or will it just append it?
    There is no automatic action. If you want to import and then append, you need to write a query to do so. In fact, in VBA, be sure to delete the old Access table of the same name before doing the import. The import will not overwrite the old table.

    do you mean to do this?

    Code:
    If Len(strInputFileName) > 0 Then
         DoCmd.TransferText acImportDelim, "Import Specification", ,(comma just before added)"tbl_Import", strInputFileName, False, ""
    I suggest looking it up in the Help file.

    And i am new to using code, I switched from macros so still learning to place things etc. where would i put the code to refrence the contents in the combo box? Im going to use a combo box which finds tables in my database.

    Code:
    Private Function GetTables()
    
    Dim Myarray As Variant
    Dim TablesSchema As ADODB.Recordset
    Dim conn As ADODB.Connection
    
    'reset cursorlocation to allow sorting in ordinal_position
       Set conn = CurrentProject.Connection
       With conn
       .CursorLocation = adUseClient
       End With
       
    'Get all database tables.
     Set TablesSchema = conn.OpenSchema(adSchemaTables)
     TablesSchema.Sort = ("TABLE_NAME")
     
     Me.cboTablesList.RowSource() = ""
     Do While Not TablesSchema.EOF
     
     'Exclude System Tables and Default Treeview Table
     If Left(TablesSchema("TABLE_NAME"), 4) = "MSYS" Or TablesSchema("TABLE_NAME") = "TREEVIEWFEED" Or Left(TablesSchema("TABLE_NAME"), 1) = "~" Then
     GoTo SKIP
     End If
     
            'Add Tables to the Combobox
            Myarray = Me.cboTablesList.RowSource()
            If Me.cboTablesList.ListCount < 1 Then
            Me.cboTablesList.RowSource = TablesSchema("TABLE_NAME")
            Else
            Me.cboTablesList.RowSource = Myarray & ";" & TablesSchema("TABLE_NAME")
            End If
    SKIP:
     TablesSchema.MoveNext
     Loop
    
    
    Set TablesSchema = Nothing
    End Function
    If the program worked with macros before, you should have simply compiled the macros into VBA. There's a utility within Access that does that for you.

    Frankly, I don't know what your code does. The probable reason is because I'm married to DAO; I never used ADO and I don't have a clue what it needs. In DAO, all this is entirely unnecessary.

    Having said that, this function is not an event procedure, which is what you need. In other words, I would start over from scratch, unless you can retrieve the old working macros and compile them, as I mentioned before.

    To black-box your logical steps, you need to do the following:
    Using the combo box wizard, create a combo box with the following properties:
    1 - the Row Source Type should be set to Value List
    2 - the Row Source (ignore the Control Source) should look like this:
    <Data Type>;<TableName>;<FileName> (don't forget the colons)
    Example:
    "Address Information";"tblTable1";"Complete Path w/filename"
    Everything in quotes, since it is all text.
    3 - the Column Count is 3
    4 - the Column Widths should be 2";0";0"
    5 - the List Width should be 2"
    Leave all other properties default.
    You should wind up with 9 rows with 3 pieces of information on each row, all separated by colons.
    When you're running the program, after selecting a data type in the combo box, click on a command button. The command button's on click event (in VBA) should do the following:

    Remember that Column() numbers begin with 0, not 1. Column(0) is the visible text portion of the combo box, or what I'm calling the data type.
    1 - wipe out the table name specified in the combo box's Column(1), if it exists.
    2 - use the TransferText construct to import the filename specified in the combo box's Column(2) into the table named in Column(1).
    3 - If you need to append the data into an existing table, write an import query to do it.
    That's all there is to it. If you need to repeat with other data types, go ahead and do so. If this needs to be done periodically with all 9 data types, you can do something else; one thing at a time.

    Sam

  6. #6
    Join Date
    Jun 2012
    Posts
    85
    thanks for the help sam, appreciate it
    Mike
    ________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •