Maybe you didn't post the whole Function.... but here are my observations (FWIW) Some comments in line.
( I did make a couple of changes .... a variable "sSQL" - makes it easier for me to read)
Code:
Option Compare Database 'should be at the top of every module
Option Explicit 'should be at the top of every module
Public Function importEMCTable()
'Vars for wksp for transactions
Dim wksp As DAO.Workspace 'For the transaction
Dim Assets As DAO.Recordset 'A join of two tables to put data into
Dim import_table_rs 'Table I am importing data from, used for do loop
' Why is import_table_rs not declared as DAO.Recordset?????
Dim iteration As Long
Dim sSQL As String
iteration = 0
'Open Assets JOIN GPCL_Asset_Details to put data into
sSQL = "SELECT Assets.Asset_ID, Assets.GPCL_Asset_ID, Assets.Asset_Type,"
sSQL = sSQL & " Assets.Manufacturer, Assets.Model, Assets.Serial_Number, Assets.Description,"
sSQL = sSQL & " GPCL_Asset_Details.GAD_ID, GPCL_Asset_Details.GAD_Asset_Link, GPCL_Asset_Details.Status,"
sSQL = sSQL & " GPCL_Asset_Details.Manual_Number, GPCL_Asset_Details.Location"
sSQL = sSQL & " FROM Assets LEFT JOIN GPCL_Asset_Details ON Assets.Asset_ID = GPCL_Asset_Details.GAD_ID"
' Debug.Print sSQL
Set Assets = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
'Open table to import data from
sSQL = "SELECT Import_ID, GPCL_Asset_ID, Asset_Type, Manufacturer, Model,"
sSQL = sSQL & " Serial_Number, Asset_Status, Location, Manual_Number "
sSQL = sSQL & " FROM EMC_Import_Table"
' Debug.Print sSQL
Set import_table_rs = CurrentDb.OpenRecordset(sSQL) '
Set wksp = DBEngine.Workspaces(0)
'
' Where is the "wksp.BeginTrans" command??? And maybe "wksp.Rollback" if there is an error??
'
'I would check to ensure there are records in the recordset or at least move to the first record in the record set
import_table_rs.MoveFirst
Do While Not import_table_rs.EOF
'Code for setting variables is performed
'Some of the fields in the MySQL can be NULL like GPCL_Asset_ID, Manufacturer, Model, Serial_Number, Description, some others
'Note I am setting primary keys manually because I need to set the Link that joins the two tables
'All fields are set, but in debug the null fields are given the value of Empty
With Assets
.AddNew
!Asset_ID = input_Asset_ID
!GPCL_Asset_ID = input_GPCL_Asset_ID
!Asset_Type = input_Asset_Type
!Manufacturer = input_Manufacturer
!Model = input_Model
!Serial_Number = input_Serial_Number
!Description = input_Description
!GAD_ID = input_GAD_ID
!GAD_Asset_Link = input_GAD_Asset_Link
!Status = input_Status
!Manual_Number = input_Manual_Number
!Serial_Number = input_Serial_Number
!Location = input_Location
.Update
End With
'To increase the Primary Keys each iteration, I add DMax and a variable called iteration. There is no conflict in primary keys.
iteration = iteration + 1
'need to move to the next record or you are stuck in an endless loop!!
import_table_rs.MoveNext
Loop
'Then I commit
wksp.CommitTrans (dbForceOSFlush)
'I also have code for closing the connections and cleaning up vars.
End Function
Out of curiosity, you opened two records sets: "Assets" and "import_table_rs". You are creating records in "Assets", but no data is being read from/written to "import_table_rs".
You have lines like:
Code:
.AddNew
!Asset_ID = input_Asset_ID
!GPCL_Asset_ID = input_GPCL_Asset_ID
It looks like the same data is being written to every new record written to "Assets".
The last part of the code confuses me... 
Can't get transaction in VBA working
Windows 7 64bit 
Access 2010 32bit
Reply With Quote
