VBA code to populate an access database

[Benjaminvh]

Hi all.

Hopefully someone will have the answer I am looking for

I have an existing databade (db1) and table (t1). I want to perform calculations for each record and then create a new database and table (db2 and t2) to dump the data in. t2 will have all the same fields as the first table, except for the added calculated fields.

The way I went about it was to assign the cell values in t1 to variables. This is useful for me since some intense manipulation needs to happen. After the calculations, I want to transfer all the cell values to t2.

I thought to try something like:

Set TempDBse = OpenDatabase([ExpPath] & [ExpFile_Temp])
Set TempTable = TempDBse.OpenRecordset([ExpTable_Temp], dbOpenTable)

With TempTable
TempTable.AddNew

TempTable("PRODUCT_CODE").Value = Prod
TempTable("AGE_AT_ENTRY").Value = AgeEntry
TempTable("SEX").Value = Sex
TempTable("IRP_MKR").Value = IRP
TempTable("SMOKER_STAT").Value = SmokerS
TempTable("DB_OCC_CLASS").Value = DisOccClass
End With
Set TempTable = Nothing

TempTable.Close
Set Table = Nothing

This isn't working. I get runtime error 91 (with block variable...). In any case, I'm new to VBA and don't know if this code is doing what I want it to.

Also, is it possible to create a table (t3) where the fields in t3 are a subset of the fields in t1 and have the variable values (e.g. Prod) dumped in the correct field (i.e. the programme won't dump a particular variable's value if its relevant field is excluded from t3)?

Thanks
Much much appreciated

 

[Dennisk]

with DAO you need to update the table

TempTable.Update.

what are you using the with for. this is how it is used

With TempTable
.(Product_Code").value=Prod

End With

 

[Benjaminvh]

Thanks for the speedy reply.

Where do I put the TempTable.Update in my code? Are you saying that the "With" is unnecessary?

 

[Benjaminvh]

Thanks so much dude. It worked when I put the TempTable.Update before the End With.

Im now trying to figure out how to do a group by query in vba so that I can reduce the number of records (as for many, the cell values are the same except for a calculated field which I want summed across all identical lives).

 

[Dennisk]

Create a new query then select totals from the view menu, this gives you an extra row to select various types of agregates, counts, sums, groupby etc

 

[Benjaminvh]

The database I want to do queries on is huge, so I'd ideally like to run these queries from VBA rather than opening the db itself.

Thanks

 

[Dennisk]

VBA is part of Access, so to run VBA you require Access.

If your db is huge, have you split it into FE/BE, keep your data only in the BE (Backend) and your Queries in the FE (Front End).

 

[Benjaminvh]

sounds cool, but i have no idea how one would achieve such a thing...

 

[Benjaminvh]

oh and I know I'm asking a lot, but one more question:

I'm writing all this code in Excel VBA. I want to delete all the records from a table so that when I paste records in it, only new records go into the table.

I tried:

Private Sub DeleteAll_Click()
Dim StrSQL As String

StrSQL = "Delete * from Your_Table;"
DoCmd.SetWarnings False (4)
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
End Sub

but got a compile error, variable not defined (I think its referring to line (4))

Is there another way to do this? Newer updates of the table will always be bigger than previous versions, so if I could modify my existing code to make new records so that it wrote over old records, that would be fine too.

 

[Dennisk]

I can't help you with Excel as this is the Access Forum