Newbie VBA: Updating Database with Recordsets 1

[Dutt]

Hello,

I just started learning vba not too long ago and I'm doing this project. What I have to do is import a table named ImportTable into the Project Master File table, but only updating the records in ImportTable that are different then whose of Project Master File.

'open the database and set the two recordset
Set dbs = OpenDatabase("C:\My Documents\Access\TestDatabase.mdb&quot
Set rstImport = dbs.OpenRecordset("SELECT * FROM ImportTable&quot
Set rstMaster = dbs.OpenRecordset("Select * FROM [Project Master File]&quot

'Import data from Temp table to the Project Master File if the Project ID equals the Project PS ID
Dim intI As Integer
intI = 1
With rstImport
Do Until .EOF
.Edit
If ![Project ID] = rstMaster![Project PS ID] Then
![Project Description] = rstMaster![Project/Service/Description]
![Project Manager] = rstMaster![Project Manager]
![Status] = rstMaster![Project Status]
![Roll Up Code] = rstMaster![RollUp Code]
![Customer ID] = rstMaster![Client PS ID]
![Project Start Date] = rstMaster![Project Start Date]
![Project End Date] = rstMaster![Project Close Date]
![CSP Billed This Year] = rstMaster![Billed This Year]
![CSP Billed Previous Years] = rstMaster![Billed Previous Years]
MsgBox "Error Checking", vbInformation, "Error Checking"
.Update
.MoveNext
intI = intI + 1
Else
MsgBox "The was a Error Importing Please try again", vbCritical, "Error Importing"
End 'exit loop while error checking
End If
Loop
End With

Not sure if I went about this the right way but if you have nything that can help me that would be great!!

If you need me to explain it better let me know

Dutt

 

[jebry]

Hi!

Maybe I misread something, but it looks like you are taking info from rstMaster and storing it in rstImport. From what I read I thought you wanted the information to flow the other direction.

hth
Jeff Bridgham

 

[Dutt]

Hi Sorry Jeff,

You right I have it backwards, but I still don't think its going to fix my problem. What do you think?

Justin

 

[Dutt]

Heres the way its supose to go!! Sorry about that

Set dbs = OpenDatabase("C:\My Documents\Access\TestDatabase.mdb&quot
Set rstImport = dbs.OpenRecordset("SELECT * FROM ImportTable&quot
Set rstMaster = dbs.OpenRecordset("Select * FROM [Project Master File]&quot

'Import data from Temp table to the Project Master File if the Project ID equals the xwave PS ID
Dim intI As Integer
intI = 1
With rstMaster
Do Until .EOF
.Edit
If ![xwave PS ID] = rstImport![Project ID] Then
![Project/Service/Description] = rstImport![Project Description]
![Project Manager] = rstImport![Project Manager]
![Project Status] = rstImport![Status]
![RollUp Code] = rstImport![Roll Up Code]
![Client PS ID] = rstImport![Customer ID]
![Project Start Date] = rstImport![Project Start Date]
![Project End Date] = rstImport![Project Close Date]
![Billed This Year] = rstImport![CSP Billed This Year]
![Billed Previous Years] = rstImport![CSP Build Previous Years]
MsgBox "Error Checking", vbInformation, "Error Checking"
.Update
.MoveNext
intI = intI + 1
Else
MsgBox "The was a Error Importing Please try again", vbCritical, "Error Importing"
End 'exit loop while error checking
End If
Loop
End With
'Delete data in the temp file-delete until end of record
dbs.Execute "DELETE * FROM " & "ImportTable;"
dbs.Close
'output the import is complete
MsgBox "Import Complete", vbInformation, "Import Complete"
Else
'call the getfile commmand button
'Call Form_frmImport.pbBrowse
End If
Set dbs = Nothing
Set rstMaster = Nothing
Set rstImport = Nothing

End Sub

Dutt

 

[jebry]

Hi!

Before I make any suggestions let me make sure I understand the problem. ImportTable contains data which you have brought in from an outside database or Excel etc. It will contain records some of which have the same ID as in Project Master File. If the ID from ImportTable does not match and ID in Project Master File, you want to add that record to Project Master File, otherwise you want to update the record in Project Master File. Is that correct?

Jeff Bridgham

 

[Dutt]

You hit it on the nail!!! I couldn't have explained it better myself!

Dutt

 

[jebry]

Okay!

Try this code:

Set dbs = OpenDatabase("C:\My Documents\Access\TestDatabase.mdb&quot
Set rstImport = dbs.OpenRecordset("SELECT * FROM ImportTable", dbOpenDynaset)
Set rstMaster = dbs.OpenRecordset("Select * FROM [Project Master File]", dbOpenDynaset)

rstImport.MoveFirst
Do Until rstImport.EOF
rstMaster.FindFirst "[xwave PS ID] = '" & rstImport![Project ID] & "'"
If rstMaster.NoMatch Then
With rstMaster
.AddNew
![xwave PS ID] = rstImport![Project ID]
etc.
.Update
End With
Else
With rstMaster
.Edit
![Project/Service/Description] = rstImport![Project Description]
etc.
.Update
End With
End If
rstImport.MoveNext
Loop

See if that does what you want it to

hth
Jeff Bridgham

 

[Dutt]

Thanks a million Jeff I'll check it out on Monday!!