Overwhelmed! Want to read 1 table, append to other.

[jetspin]

Hi.

Pardon, but I have some VB and VBA background but am overwhelmed by the possibilities to do a simple task. Any information to point me to a sample etc.or explanation would be appreciated greatly. THANKS!

I want to update my current client table with an excel spreadsheet of new clients. My current client table has some many to one relationships with other tables. For example,,, to a states table for address information,,, and
to a status table... Here is what I have done so far....

This is a one time run. I found I could not map fields using the import wizard, so I imported the new data into a a new work table which I will use as a temporary work table to read from.

I don't recommend this next approach, but I want to explain it just to show where I am at. I know what I really want is some kind of append query.

I found I could create a combo box on my form for the current client table to lookup clients from the work table
and then add code to the 'on click' event to 'pull' the data from the work table to the current client table. This works fine except for I could not write the correct code to look up states code to get the state pointer for this many to one relationship. I was going to use a timer and record navigation to have this run in a rube goldfarb fashion to advance and click on each succeeding combo box selection... This is not the best way,,, I don't recommend this, but it was my only approach...Here's my on-click event for the combo box. I have not added the record navigation or timer yet.

Dim wrkstring As String
If Me.cmbCustomer.Column(0) <> &quot;&quot; Then Me.cliLastName = Me.cmbCustomer.Column(0)
'Me.cliLastName = Me.cmbCustomer.Column(0)
If Me.cmbCustomer.Column(1) <> &quot;&quot; Then Me.cliFirstName = Me.cmbCustomer.Column(1)
If Me.cmbCustomer.Column(2) <> &quot;&quot; Then Me.cliCompanyName = Left(Me.cmbCustomer.Column(2), 30) Else Me.cliCompanyName = &quot;&quot;
If Me.cmbCustomer.Column(3) <> &quot;&quot; Then Me.cliTitle = Me.cmbCustomer.Column(3) Else Me.cliTitle = &quot;&quot;
If Me.cmbCustomer.Column(4) <> &quot;&quot; Then Me.cliAddress1 = Me.cmbCustomer.Column(4) Else Me.cliAddress1 = &quot;&quot;
If Me.cmbCustomer.Column(5) <> &quot;&quot; Then Me.cliCity = Me.cmbCustomer.Column(5) Else Me.cliCity = &quot;&quot;
If Me.cmbCustomer.Column(6) <> &quot;&quot; Then
Me.cliTitle = Me.cmbCustomer.Column(6)
Else
Me.cliTitle = &quot;&quot;
End If
'Next is state to looup in table to get the state pointer...
If Me.cmbCustomer.Column(7) <> &quot;&quot; Then
wrkstring = Me.cmbCustomer.Column(7)

''''Next statemetn does not work to get the state..............

Me.cliSteID = SELECT tblStates.steSteID FROM tblStates WHERE (((tblStates.steCode) = wrkstring))
Else
Me.cliSteID = 52 'this is blank
End If

I now realize this is not the best way, So now I am looking for sample of code and where to put it to simply read my work table and append it to my current table while being able to set the correct many to one pointers for my client states and status'

I see ADO, I ser append queries and recordsets,,, I know when I find the code, I will probably put it on a temp button on my form for the client table. This is a one time run. Thanks again. I have searched the access faq, newsgroups etc.

Jetspin

 

[jetspin]

Hi.

I am the original poster. I had been researching this issue for several days and discovered the solution... so thought I should post it.

After searching all over the net and some help from
Balters's Access 2000 Dev book.

The problem was to load a current 'Client' table from an excel spreadsheet. The 'Client' table also has several many-to-one relationships to several other tables that further define a client. Some examples... Address State Abbrevs, Client Status, Contact Type.

So there were two issues...

1.) How to map the data field by field from the excel spreedsheet to the current Client Table.

2.) How to map the data using relationships to store the table pointers for the relationships. i.e pointer to the state table for state abbrevs in address using the 2 letter state code stored on the excel spreadsheet.

I did the &quot;get external data' of the excel spreadsheet to create a WORK Client Table which will be temporary.

I used 'create query in design mode' and (THIS IS IMPORTANT) selected the the State table, the Client Status Table, Contact Type and the WORK Client table . All of these are for the input side of the query. I then dragged the state, status and client type data fields from the WORK client table to the other tables to establish there relationships. This does the lookups to get the correct ID's!!!!

I then selected from the top toolbar, Query, Append Query and selected the Clients Table which this data will get appended to. This creates an append query.

I then draged the fields down to the lower grid and selected the matching fields in the 'Append To'. For the relationship tables, I dragged the ID's (or pointers) and not the data to set the relationships.

(A side note,, on some 'not required fields' on the client table, I had to set 0 length okay.)

I ran the query and it worked. It set the correct pointers for all the relationship tables too.

Fred