Importing data into downloaded template

Question

I am attempting to use a charitable contributions template that I downloaded from Microsoft.  I am trying to import data from another database but realized that the toolbar that contains has the Home, Create, External Data and Database Tools options is missing from the template.  How do I import data?

Answer 1

You cannot change a value in an autonumber column, but the values are really immaterial as they are solely to provide a distinct identifier per row and have no semantic relevance whatsoever.  The only way to renumber them would be to delete the column from the table definition, save the table, and then add the column back to the table definition.  It would then be assigned new numbers.

But if sequential numbering is important you should not use an autonumber, but compute the next number in sequence when a new row is inserted via a form.  Roger Carlson has a simple solution for this, catering for both single and multi-user environments, at:

http://www.rogersaccesslibrary.com/forum/topic395.html

and there's a more complex one of mine, which allows for the next number to be used to be 'seeded' at any time at:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23839&webtag=ws-msdevapps

Unlike Roger's mine is not intended to guarantee sequential numbers, however, but only ordered numbers and operates in the same way as an autonumber in that if the insertion of a record is aborted the number will not be re-used for the next record.

I've also amended Roger's to allow numbers to be seeded, by changing his GetProductID() function in the product_2 form's module as follows:

Private Function GetProductID()

    Dim lngProductID As Long

    Dim lngSeedID As Long

    lngProductID = Nz(Dmax("ProductID", "Product"), 0) + 1

    lngSeedID = Nz(Dlookup("Seed", "Seeds"), 0)

    If lngSeedID <= lngProductID Then

        GetProductID = lngProductID

    Else

        GetProductID = lngSeedID

    End If

End Function

This requires the addition of a one-row table, Seeds, to the database, with a single column, Seed, of long integer number data type.  Enter a row with a zero value to start with.  The number you wish to 'seed' is entered into this table, for which a simple form can be designed, setting its AllowAdditions and AllowDeletions properties to False (No) so that only the one existing row can be edited.   To open this form I've added a button to the Product2 form with the following code in its Click event procedure:

    DoCmd.OpenForm "frmSeeds", WindowMode:=acDialog

    Me.ProductID.DefaultValue = """" & GetProductID & """"

This amendment to Roger's solution makes mine pretty much redundant.  There could be some situations where mine is more bullet-proof, principally that the user will see the new number when they begin to insert the new record and it will not change, whereas with Roger's, if there is a conflict the number will change for any user who is not the first to save the record with the new number; but by and large Roger's solution, amended as above to allow seeding, will be fine and is a lot simpler to implement.

If you wanted you do this you'd first have to renumber the existing rows by removing and then replacing the column as described above.  Then add another column of straightforward long integer number type and use an update query to fill this column with the values from the autonumber column.  You could then remove the autonumber column, make the new column the primary key and use Roger's method to insert the next number when adding new records via a form.

Answer 2

The only Required column in the Contributors table is [First Name], so you must have a value in that column for every row in the imported table.  Apart from that there are no constraints other than the primary key ID column, which must of course have distinct value.  If you omit this then Access will automatically insert distinct values, so this will work:

INSERT INTO Contributors([First Name], [Last Name])

VALUES("Ken","Sheridan");

but this won't:

INSERT INTO Contributors([Last Name])

VALUES("Sheridan");

because of the lack of a First Name value.  This will be reported as a validation rule violation.

Its sometimes not realized that you can insert values into an autonumber column provided they are distinct, so:

INSERT INTO Contributors(ID, [First Name], [Last Name])

VALUES(42, "Ken","Sheridan");

will work provided there is not already a row with an ID value of 42.  But if I then try:

INSERT INTO Contributors(ID, [First Name], [Last Name])

VALUES(42, "Fiona","Sheridan");

This will fail and a key violation error will be reported. But if I omit the ID column:

INSERT INTO Contributors([First Name], [Last Name])

VALUES("Fiona","Sheridan");

This will work and Access will automatically give the ID column a distinct value.

If you do have empty first names in the imported table you can replace these with a default value, e.g. N/A in place of these by changing the 'field' row of the column in the query to:

IIF(TRIM([First Name] & "") = "","N/A",[First Name])

Access will call the column something like Expr1, but that's immaterial.  Change First Name to whatever the real name of the column in the imported table is if necessary.

To avoid key violations, omitting the ID column from the query should do this, and Access will insert distinct values, which is fine so long as you don't need to retain original ID values to relate the table to another table into which you are also inserting rows from another imported table.

Answer 3

Provided the fields in the imported table of contacts match some of the fields in the Contributors table in data type you should be able to append them to the Contributors table using an 'append' query.  The steps to do this are:

1.  Open the Charitable Contributions database while holding down the Shift key as John described.

2.  Select Query Design on the Create ribbon.

3.  The query design window will open along with t 'Show Table' dialogue.  In the latter select your imported table and click Add.  Then close the dialogue.

4  Add the fields from the table to the query design grid one by one.

5. On the Design ribbon  select Append as the Query Type.  The Append dialogue will open.  Select the Contributors table from the drop down list.

6.  In each column of the query design grid, in the Append To row select the field in the Contributors table to which you want the field from your imported table to be appended.  The field names don't have to be the same, just the same data type, e.g. if you have a text field named Surname in the imported table you'd append this to the Last Name field in the Contributors table.

7.  Finally click on the Run icon on the Design ribbon (the big red exclamation mark near the left of the ribbon).  A dialogue will open telling you how many rows will be appended.  When you confirm that you want to go ahead, all things being well, the rows should be appended into the Contributors table and you'll see them there when you open it.  If there are problems with any rows during the append you'll get a message box telling you how many rows cannot be appended, along with a rather cryptic reason why.

If you have fields in your imported table which have no equivalent in the Contributors table you can open the latter in design view and add new fields.  Your data could then be appended to them in the above process, but they won't appear in any forms or reports in the database unless you append them, and their underlying queries, so you'd need to do a bit more work to make it fully usable if you have added additional fields.

Answer 4

Thanks!  Obviously I am new to Access.  I was able to import the database but when I try to list of names and contact info into the contributors list, it gives me paste errors since the fields are different.  Is there some way to copy only what matches or tell it to create new fields in the contributors table?

Answer 5

Unless the template is an .accde file - which turns off most of the design tools - you should be able to get to these tools by holding down the Shift key as you open the database.