How to get data from one table to another table using the vba code

[DenShin]

image in the right is the criteria on when the process of copying will trigger. First, based from the value of the "Select Here" which is the "CODE" in the fields what ever the code matches on the table will be copied. Two, the checkbox is a trigger also to what table will be the source and destination table will the data will insert into. By the way all tables are link to sql server. can anyone help what codes will be used here?

Noted: Source tablename is definite with the "@" sign of the name.

used codes:
CurrentDb.Execute "INSERT INTO [Temp_promo] SELECT fields1,fields1,fields3 FROM [@Promo] WHERE [code] = '" & Me.cmbcode & "';" - doesn't work
Run-time error '3622' - You must use the dbseechanges opetion with openrecordset when accessing a sql server table that has an identity column.

CurrentDb.Execute "INSERT INTO [Temp_promo] SELECT fields1,fields1,fields3 FROM [Promo] WHERE [code] = '" & Me.cmbcode & "';" - This work but i need to changed the "@PROMO" table to "PROMO" table, which as i said must definite to "@PROMO" only.


Private Sub Copy_Click()
Dim db As Database
Dim rsSource As Recordset
Dim rsDestination As Recordset

If Shw1 = True Then
'Open a recordset for the source table'
Set db = CurrentDb
Set rsSource = db.OpenRecordset("SELECT Code,Name FROM [@Promo] WHERE [code] = '" & Me.cmbcode & "';", dbOpenDynaset, dbSeeChanges)



'Open a recordset for the destination table'
Set rsDestination = db.OpenRecordset("TEMP_PROMO", dbOpenDynaset, dbSeeChanges)

'Check if there are records in the source Recordset '
If Not rsSource.EOF Then
rsSource.MoveFirst 'Move to the first record in the source Recordset'

'Loop through records in the source Recordset'
Do Until rsSource.EOF
'Add a new record to the destination Recordset'
rsDestination.AddNew


'Set values in the destination Recordset based on the source Recordset'
rsDestination!Code = rsSource!Code
rsDestination!Name = rsSource!Name
'Repeat for other fields...'
'Update the destination Recordset'
rsDestination.Update

'Move to the next record in the source Recordset'
rsSource.MoveNext
Loop

End If
rsSource.Close
rsDestination.Close
Set rsSource = Nothing
Set rsDestination = Nothing
Else
'Do nothing'
End If
- this work but for only one table only to another only. adding more will not work.
annyone can help me about this? very much appriciated

[June7]

Not sure this is any better explanation than cross-posted thread https://stackoverflow.com/questions/...75238_77582392
Last comment of that thread:
"I just tested running an INSERT SELECT on my linked SQLServer table and also get that error when usingCurrentDb.Execute.
DoCmd.RunSQLgenerates different error related to timestamp field. Does your table have timestamp field? How many fields are involved?
Suggest you explicitly reference fields in the INSERT and SELECT parts - worked for me."

Can concatenate table name into SQL statement.


Should post code between CODE tags to retain indentation and readability.

[DenShin]

Yeah its the same question from the cross-posted. But I can't explain well to the other thread, Cause I can't show the picture of what i'm trying to do. Yes, I do have a timestamps on the fields 3 dates but from the @PROMO only, other tables has no timestamp included.

fields varies, but max of 16 fields and min of 6 fields per table:

(Checked box: Headers (if True)) = From (@Promo - 16 fields - 3 Time Stamps - 1 Auto Increment) insert into (TEMP_PROMO) where Code = '" & me.txtcode &"';"

(Checked box: Area/s (if True)) = (@Promo_Area - 6 fields - 0 Time Stamps - 0 Auto Increment) insert into (TEMP_PROMO_AREA) where Code = '" & me.txtcode &"';"

(Checked box: Business Partner/s (if True)) = (@Promo_BP - 6 fields - 0 Time Stamps - 0 Auto Increment) insert into (TEMP_PROMO_BP) where Code = '" & me.txtcode &"';"

(Checked box: Main Items/s (if True)) = (@Promo_Details - 13 fields - 0 Time Stamps - 0 Auto Increment) insert into (TEMP_PROMO_BP) where Code = '" & me.txtcode &"';"

(Checked box: Exempted Prom (if True)) = (@Promo_Exempted - 5 fields - 0 Time Stamps - 0 Auto Increment) insert into (TEMP_PROMO_EXEMPTED) where Code = '" & me.txtcode &"';"

(Checked box: Free Item/s (if True)) = (@Promo_Free - 13 fields - 0 Time Stamps - 0 Auto Increment) insert into (TEMP_PROMO_FREE) where Code = '" & me.txtcode &"';"

that is how the needed codes to work out.

[June7]

What do you mean by "timestamp"? I mean a timestamp (aka rowversion) type field in SQLServer. Actually has nothing to do with a date/time data type. AFAIK, table can have only one of these fields.

You need to build SQL statement(s) based on selections on form? If user checks any or all of checkboxes, run SQL to insert records from/to associated tables - up to 6 table pairs? So need some sort of looping code structure. Consider a multi-select listbox instead of multiple checkboxes. Looping through selected listbox items would be simpler.

I don't really understand this statement "This work but i need to changed the "@PROMO" table to "PROMO" table, which as i said must definite to "@PROMO" only."

[Welshgasman]

Possible crosspost here ? https://www.access-programmers.co.uk...by-vba.329563/

If not, new advice.

[pbaldy]

Have you tried

CurrentDb.Execute "INSERT INTO [Temp_promo] SELECT fields1,fields1,fields3 FROM [@Promo] WHERE [code] = '" & Me.cmbcode & "';", dbSeeChanges

[DenShin]

Yes @pbaldy, this works just fine for the first checkbox which is the Header. But using that code for the next check box nothing seems to work, adding the dbSeeChanges or removing it in the end of the statement.. it doesn't work anymore. Why is that?
sample of my code upon clicking the copy button.

If chckbox1 = true then
CurrentDb.Execute "INSERT INTO [Temp_promo] SELECT fields1,fields1,fields3 FROM [@Promo] WHERE [code] = '" & Me.cmbcode & "';", dbSeeChanges
else
'do nothing
end if

If chckbox2 = true then
CurrentDb.Execute "INSERT INTO [Temp_promo_details] SELECT fields1,fields1,fields3 FROM [@Promo_details] WHERE [code] = '" & Me.cmbcode & "';", dbSeeChanges "with or without"
else
'do nothing
end if

If chckbox3 = true then
CurrentDb.Execute "INSERT INTO [Temp_promo_Free] SELECT fields1,fields1,fields3 FROM [@Promo_Free] WHERE [code] = '" & Me.cmbcode & "';", dbSeeChanges "with or without"
else
'do nothing
end if


and so on.....

i tried also puting some msgbox "" to check if the code runs correctly. and the process is correctly executed by why is that nothing copied from one table to another once the checkbox is checked from main item/s until exempted.

i also tried checking the main item/s but executed well but nothing copied from the @promo_details table to temp_promo_details.

[DenShin]

Im sorry June7 I don't have any slightest idea on what timestamp is used.

And Yes, i need to build vba codes or sql statement as long it runs if the checkbox being checked and table to table will be copied from source to destination table. this can be 1 checkbox selected or all the checkbox selected.

Just ignore the last statement.. heheh

[Gicu]

Have you tried to see if the Select statements for the other checkboxes return any records? Debug.Print just the Select part of the Insert statements, then copy it from the intermediate window into a new query and run it.
Can you please show us a screenshot of the navigation pane of your Access front-end with the two sets of linked tables? Is this Access file used for anything else other than this copying task? You can very easily loop through the tabledefs in code and rename the "@xxx" as "s_xxx" (for "source"):

Public Sub vcRenameLinks()
Dim tdf As DAO.TableDef
Dim db As DAO.Database          
    
Set db = CurrentDb                      
   
For Each tdf In db.TableDefs
	If Len(tdf.Connect) > 0 Then
		If Left(tdf.Name, 1) = "@" Then tdf.Name = Replace(tdf.Name, "@", "s_")
		tdf.RefreshLink		  
	End If
Next tdf


End Sub

Maybe post the exact code (the entire sub) you are trying to run and not the "Field1,Field2,..." pseudo code.
Cheers,

[pbaldy]

For chuckles, try this:

, dbSeeChanges + dbFailOnError

It may throw an error that tells you what's wrong.