• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Need help is editing MS access 2007

Need help is editing MS access 2007 .
I have a file I need to import data into it from a text/excel  file.

I need to add a button on the table to import from there only.

Your help is highly appreciated.

Regards
0
MAS (MVE)
Asked:
MAS (MVE)
  • 17
  • 14
  • 4
2 Solutions
 
MAS (MVE)Technical Department HeadAuthor Commented:
I am attaching the files
bbformat20001.mdb
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
Attaching the file to import
Inetpub.zip
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
MAS (MVE)Technical Department HeadAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
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

0
 
MAS (MVE)Technical Department HeadAuthor Commented:
Or you recommend how to import it as you have both the files.

Thnaks
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
Can you help me to add a button on the first BBFORMAT  to import data to BB_FORMAT.

Thanks
0
 
JezWaltersCommented:
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

Open in new window

0
 
JezWaltersCommented:
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!  :-)
0
 
JezWaltersCommented:
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
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Here's a method you can use to do this:

http://www.mvps.org/access/api/api0001.htm

This is simple to integrate, and should be simple for you to do.
0
 
JezWaltersCommented:
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

Open in new window

0
 
JezWaltersCommented:
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
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
When I try to import it is not importing or updating the current one.

Please help ASAP as it is now almost finsihed.

0
 
JezWaltersCommented:
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)?
0
 
JezWaltersCommented:
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.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
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
0
 
JezWaltersCommented:
Sorry, but you're not explaining what's going wrong.  Did you get my sample database (posted in ID 26464242 above) to work?
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
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.



0
 
MAS (MVE)Technical Department HeadAuthor Commented:
file attached
Salary-2009-04.txt
0
 
JezWaltersCommented:
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.
0
 
JezWaltersCommented:
Something tells me you are expecting the new records to be displayed on the form, but this is not what your question  is asking.
0
 
JezWaltersCommented:
Your latest file does not have the same format as your original file.  Is this the format you now wish to support?
0
 
JezWaltersCommented:
Is this the behaviour you are after?
bbformat20001.mdb
0
 
JezWaltersCommented:
I've amended the import specification to match your latest salary file (posted in ID 26472638 above), and also changed the import prompt to only specify an initial directory (instead of a file).  Lastly, I've made the form requery its data after the import has completed.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
Yes it worked. but a final tuning
I want to import and replace the current data not duplicate.


0
 
MAS (MVE)Technical Department HeadAuthor Commented:
I really appreciate your help.
There should not be duplicates after importing.



0
 
JezWaltersCommented:
You don't have any unique indexes on your BB_FORMAT table, so how are you identifying records that already exist?
0
 
JezWaltersCommented:
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.
0
 
JezWaltersCommented:
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.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
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.

0
 
JezWaltersCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell┬« is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 17
  • 14
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now