Need help is editing MS access 2007

I am attaching the files
bbformat20001.mdb

Attaching the file to import
Inetpub.zip

You can use the builtin utility for this. Select the External Data tab from the ribbon, then select the appropriate file type from there. You'll be able to locate the file to import, and you then simply follow the prompts.

I tried it before but it didn't work.
It is showing some errors. Please guide me how to import

I need to add button in the main page (main table) to import the file.

Thanks

What do you mean by "didn't work"? Did you get any errors? Can you explain exactly how you tried to import?

The import utility in Access is generally pretty good. Or, you can link the Excel file into your Access database, then use standard queries to move the data to your "live" tables. This often provides you much greater control over the process, if you need it.

Can you guide me how to do link the excel file to the access file/table as the excel file will get updated every month and I need to see the data in the excel in the 'bbformat' table.

Thanks

Or you recommend how to import it as you have both the files.

Thnaks

Locate the Link - External Data tab in the Ribbon. Click this, and follow the prompts to link your Excel worksheet to your Access database.

This will show you the "live" data in your worksheet, so as users change data in that worksheet, your Access database will "see" those changes.

Can you help me to add a button on the first BBFORMAT  to import data to BB_FORMAT.

Thanks

You should be able to achieve what you're after by adding the code below to the On Click event handler of the appropriate command button.

To get this to work, you'll have to also create the required Import Specification (which I saved using the name "BB_FORMAT Import Specification").  Are you familiar with how to perform this last step?

DoCmd.TransferText acImportDelim, "BB_FORMAT Import Specification", "BB_FORMAT", "C:\Temp\ExpertsExchange\Salary 2009_04.txt", False

Select allOpen in new window

Incidentally, you can set up control tips just by setting the "ControlTip Text" property of the relevant form controls.  It's a lot less hassle than using text boxes and Mouse Move event handlers!  :-)

Anyway, I've updated your sample database to show how to import your text file as I've described - though you'll certainly have to amend the import directory.
bbformat20001.mdb

first of all thanks a lot.
Is it possible to have a popup to browse for the text file to import.

I was having that option before. Unfortunately my external HDD stopped working where I stoed that file.

Thanks
MAS

I suspect you'll find using the Application.FileDialog object a bit easier than fiddling around with Windows API calls.

To do this you need to add a Reference to the Microsoft Office 12.0 Object Library, after which the following code should get you started:

Private Sub cmdImportData_Click()
    Dim fldFileDialog As FileDialog

    Set fldFileDialog = FileDialog(msoFileDialogOpen)
    With fldFileDialog
        .ButtonName = "Import"
        .InitialView = msoFileDialogViewDetails
        .Title = "Select file to import"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Text files", "*.txt"

        If .Show Then  ' File selected
            DoCmd.TransferText acImportDelim, "BB_FORMAT Import Specification", "BB_FORMAT", .SelectedItems(1), False
        End If
    End With
End Sub

Select allOpen in new window

I've updated the BB_FORMAT form of your sample database to show both bits of code in action (via the cmdImportData button).  It also occurred to me that it would be worth setting the initial file too, so I've done that as well this time.
bbformat20001.mdb

When I try to import it is not importing or updating the current one.

Please help ASAP as it is now almost finsihed.

Are you saying that the import appears to work (i.e. without generating an error), but no records are added to your BB_FORMAT table?

Have you tried debugging my updated sample database (posted in ID 26464242)?

I'm trying to establish whether you can't get my sample database to work, or whether this is a problem re-implementing what I proposed in your production environment.

What I want is, when I browse and select a file (txt or xls) to improt it should update the current database.  Everymonth we will send this to bank.

Thanks

Sorry, but you're not explaining what's going wrong.  Did you get my sample database (posted in ID 26464242 above) to work?

Yes It got it (downloaded from 26464242)
But when I try to import a popup is coming and I selected the file to import but nothing changed.
It is same as before.

file attached
Salary-2009-04.txt

OK, I've just re-run my latest sample database (posted in ID 26464242 above), and opened your CO_IDENTIFICATION form.  After selecting Bank Code 001, I clicked the Edit Data button and the BB_FORMAT form was displayed.

I then clicked the Import Data button in your BB_FORMAT form and it prompted for the file to import.  I selected your Salary 2009_04.txt file (posted in ID 26169993 above) and successfully added 43 records to the BB_FORMAT table.

Before clicking the Import Data button there were 48 records in the table; afterwards there are 91 records.

Something tells me you are expecting the new records to be displayed on the form, but this is not what your question  is asking.

Your latest file does not have the same format as your original file.  Is this the format you now wish to support?

Is this the behaviour you are after?
bbformat20001.mdb

Yes it worked. but a final tuning
I want to import and replace the current data not duplicate.

I really appreciate your help.
There should not be duplicates after importing.

You don't have any unique indexes on your BB_FORMAT table, so how are you identifying records that already exist?

You'll have to create a temporary importing table to achieve what you want, so that you can compare the imported data to the existing records.

By the way, you don't have any relationships defined between your tables.  Because of this you can create BB_FORMAT table records that don't have a corresponding record in your LOCAL_BANKS table.

Will you provide a link to learn on how to add that codes to the access files/database which you did and gave me.

Thanks a lot for your time and effort.

I'm afraid I don't have any links to give you, but you should be able to read about importing and opening files via the Microsoft Access on-line help.  Just click on the 'TransferText' and 'FileDialog' text and press F1.