[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help is editing MS access 2007

Posted on 2010-01-04
38
Medium Priority
?
410 Views
Last Modified: 2013-11-27
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
Comment
Question by:MAS
  • 17
  • 14
  • 4
35 Comments
 
LVL 28

Author Comment

by:MAS
ID: 26169974
I am attaching the files
bbformat20001.mdb
0
 
LVL 28

Author Comment

by:MAS
ID: 26169993
Attaching the file to import
Inetpub.zip
0
 
LVL 85
ID: 26170080
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Author Comment

by:MAS
ID: 26170237
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
 
LVL 85
ID: 26170254
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
 
LVL 28

Author Comment

by:MAS
ID: 26188319
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
 
LVL 28

Author Comment

by:MAS
ID: 26189121
Or you recommend how to import it as you have both the files.

Thnaks
0
 
LVL 85
ID: 26189122
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
 
LVL 28

Author Comment

by:MAS
ID: 26276266
Can you help me to add a button on the first BBFORMAT  to import data to BB_FORMAT.

Thanks
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 26462978
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26463074
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26463120
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
 
LVL 28

Author Comment

by:MAS
ID: 26463290
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 26463565
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26464110
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26464242
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
 
LVL 28

Author Comment

by:MAS
ID: 26471418
When I try to import it is not importing or updating the current one.

Please help ASAP as it is now almost finsihed.

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 26472213
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26472221
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
 
LVL 28

Author Comment

by:MAS
ID: 26472457
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
 
LVL 17

Expert Comment

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

Author Comment

by:MAS
ID: 26472510
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
 
LVL 28

Author Comment

by:MAS
ID: 26472638
file attached
Salary-2009-04.txt
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 26472659
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26472663
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26472686
Your latest file does not have the same format as your original file.  Is this the format you now wish to support?
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 26472773
Is this the behaviour you are after?
bbformat20001.mdb
0
 
LVL 17

Accepted Solution

by:
JezWalters earned 800 total points
ID: 26472788
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
 
LVL 28

Author Comment

by:MAS
ID: 26473162
Yes it worked. but a final tuning
I want to import and replace the current data not duplicate.


0
 
LVL 28

Author Comment

by:MAS
ID: 26473252
I really appreciate your help.
There should not be duplicates after importing.



0
 
LVL 17

Expert Comment

by:JezWalters
ID: 26473295
You don't have any unique indexes on your BB_FORMAT table, so how are you identifying records that already exist?
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 26473302
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26473335
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
 
LVL 28

Author Comment

by:MAS
ID: 26473367
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 26474585
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question