Import XL and Update fields (1 Viewer)

Sparky Fluff

New member
Local time
Today, 17:47
Joined
Jul 12, 2007
Messages
1
Hi,
I am using the following code to import excel spreadsheet into Access

Private Sub Cmd_Import_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"PolicyTemp", "U:\QA\Sample_Input.xls", True, "Sheet1!"
End Sub

After the import, I need to update certain fields in the PolicyTemp table depending on the values of other fields that have been imported. Can someone help me do it?
Thanks.
 

vodafrog

Registered User.
Local time
Today, 22:47
Joined
Jul 8, 2007
Messages
32
You could create various update queries to carry out the required updates and then use the following code to execute a query: -

DoCmd.OpenQuery "queryname", acNormal, acEdit

repeat the instruction for each query you wish to run.

You may wish to turn warnings off before you start to run any queries

DoCmd.SetWarnings False

Don't forget to turn them back on when you have fnished

DoCmd.SetWarnings True
 

cpremo

Registered User.
Local time
Today, 15:47
Joined
Jun 22, 2007
Messages
50
It really depends on what you're trying to do, you could use a query based on the temp table using appropriate selection parameters and "update the appropriate records. Or you could use a record set as shown below. We really need more information to give you specific guidance.

Record set example
**********************************************************
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim strField As String

Dim strLevel As String

Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

'These statements open a record set for each of the tables referenced. Each recordset
'is used within this function. Once the function/step is completed, the recordset is closed.
rst1.Open "[qfltMasterServer]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
rst2.Open "[Partitions]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

'This process runs through the recordset and deletes any record in the "Partitions" table
'that matches the criteria of the "If" statement.
Do While Not rst1.EOF
If rst2.EOF Then Exit Do
rst2.MoveFirst
Do While Not rst2.EOF
strField = rst2![Partition Name]
If strField = rst1![Partition] Then
rst2.Delete
rst2.Update
End If
rst2.MoveNext
Loop
rst1.MoveNext
Loop

'This closes the open record sets.
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
 

Users who are viewing this thread

Top Bottom