Need code to update records in a table

S

sapphire

Hi all,

I receive an excel spreadsheet that I link to my Access database. The
spreadsheet is a mainframe report so it's not formatted properly. Only the
first record for each client has the client name/client id and I need all
records to have a clientname/clientid. These reports are very large and I
need to automate formatting them so I can use them in other queries. I've
placed some dummy data below to show what the data looks like.
0Client Name Client ID Co Policy Cov Transaction
DOE, JOHN X-A01001876J-11 BBL U1002966L 1 New Business
BBL U1002966L 2 New Business
BBL U11100713L 1 Surrender

DOE, JANE X-LL5010025R-11 BBL AA0029822L 1 New Business
BBL DJ1002505X 1 Surrender

CLIENT, ONE G-LL5051008R-11 BBL U00M22299L 1 New Business
BBL KL1005805Y 1 Surrender

CLIENT, TWO K-LL2004460R-11 BBL GG00302265 1 New Business
BBL DM4452000S 1 Surrender
BBL LL0482000S 1 Surrender
BBL LL0482000S 2 Surrender

CLIENT, THREE D-UU629002S -11 UUL YY8055040 1 New Business
UUL KK029026P 1 Lapse

My searches for existing code or full examples of how to update data using a
recordset have not been successful. Any assistance will be greatly
appreciated.
 
M

Mr. Smith

sapphire said:
Hi all,

I receive an excel spreadsheet that I link to my Access database. The
spreadsheet is a mainframe report so it's not formatted properly. Only the
first record for each client has the client name/client id and I need all
records to have a clientname/clientid. These reports are very large and I
need to automate formatting them so I can use them in other queries. I've
placed some dummy data below to show what the data looks like.
0Client Name Client ID Co Policy Cov Transaction
DOE, JOHN X-A01001876J-11 BBL U1002966L 1 New Business
BBL U1002966L 2 New Business
BBL U11100713L 1 Surrender

DOE, JANE X-LL5010025R-11 BBL AA0029822L 1 New Business
BBL DJ1002505X 1 Surrender

CLIENT, ONE G-LL5051008R-11 BBL U00M22299L 1 New Business
BBL KL1005805Y 1 Surrender

CLIENT, TWO K-LL2004460R-11 BBL GG00302265 1 New Business
BBL DM4452000S 1 Surrender
BBL LL0482000S 1 Surrender
BBL LL0482000S 2 Surrender

CLIENT, THREE D-UU629002S -11 UUL YY8055040 1 New Business
UUL KK029026P 1 Lapse

My searches for existing code or full examples of how to update data using a
recordset have not been successful. Any assistance will be greatly
appreciated.
Click to expand...

You can use the code below to read from you're attached XL worksheet and
write new records to an Access table. (I've added as many comments as i can
think of to help out too. Read them as they contain further instructions)
What you do with this table is then entirely up to you but the data will be
in the format you're looking for. Also I have assumed you are working wit
and MDB file and not an ADP. If you have queries please feel free to email
me at: mr dot smith at trearc dot com.

Good luck.

**************************************
Function ReadRecsfromXL()
On Error GoTo EH

Dim db As database
Dim rstXL As Recordset
Dim rstACC As Recordset
Dim strClientID As String
Dim strClientName As String
Dim strCO As String
Dim strPolicy As String
Dim strCov As String
Dim strTransaction As String

'Set up your recordsets
Set db = CurrentDb()
'Change the name 'XLTableName' to whatever your Linbked XL table called
Set rstXL = db.openRecordset("XLTableName", dbOpenSnapshot)
'Create a Table called 'tblXLImport' (See below for field names)
Set rstACC = db.openRecordset("tblXLImport", dbOpenDynamic)

'If theres nothign in the XL spreaddie, don't bother trying to load it
If rstXL.EOF Then Exit Function

'make sure you moe to the start of recordsets, probably not necessary
but good practice
rstXL.MoveFirst
rstACC.MoveFirst

'Monitor for the end of the XL table as you loop thru its records
Do While Not rstXL.EOF

'This captures the current Client ID and Name so you can use it
'when the subsequent records don't have them.
'(Also the Nz handles either zero lengths strings OR nulls in yor XL
'cells as i don't know what you might have in there if you're
'getting this from a MF)
If Nz(rstXL![Client Name], "") <> "" Then

strClientName = rstXL![Client Name]
strClientID = rstXL![Client ID]

End If

'This IF ensure that you only create a new record in you access
table when the
'is data in the XL table to be written
If Nz(rstXL![CO], "") <> "" Then

'in the table tblXLImport I told you to create, add the fields
ClientName
'ClientID CO Policy Cov and Transaction so that this works. add
a generic
'autonumber field to use as an Primarykey/ID field too
With rstACC
.AddNew
![ClientName] = strClientName
![ClientID] = strClientID
![CO] = rstXL![CO]
![Policy] = rstXL![Policy]
![Cov] = rstXL![Cov]
![Transaction] = rstXL![Transaction]
.Update
End With

End If

Loop

'Close the recordsets
rstXL.Close
rstACC.Close

EH:
End Function

'End Code

****************************************
 
Top