0

Each day ~5000 records are uploaded to tblRecordsCurrent, at some point within the next few days when those records have been processed, they need to be moved to tblRecordsHistorical. Each record has a Foreign Key DataSetID that ties it to the date/time it was uploaded (Parent Table).

How, within vba, can I insert a single DataSet of tblRecordsCurrent into the tblRecordsHistorical from tblRecordsCurrent. I cannot insert all columns as both tables contain persisted columns.

I can't put the entire INSERT INTO tblRecordsHistorical A, B, C, D, E, F... as it is too long for access vba.

Any ideas?

aSystemOverload
  • 2,994
  • 18
  • 49
  • 73
  • 1
    How can a list of the columns be too long? See http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx which says that it allows 64,000 characters. Is your problem the record selection rather than the column selection? – Fionnuala Jul 16 '12 at 10:23
  • It is too long for VBA, not the statement. The limit per line with in vba is not that high. – aSystemOverload Jul 16 '12 at 10:34
  • 2
    No it is not :) Have you used line continuation characters? There is a limit there, but it is very easy to get around `sSQL=SSQL & " More stuff"` – Fionnuala Jul 16 '12 at 10:35

1 Answers1

3

If you save the query in your Access database, then you can execute in in VBA the following way:

DoCmd.OpenQuery "yourQueryName", acViewNormal, acEdit

Or

CurrentDb.OpenRecordset("yourQueryName")

Or

CurrentDb.Execute "qryAddLoginfoRow"

This allows you to execute the query without having the query stored in your VBA code. But you are also able to execute this via a query in VBA:

INSERT INTO tblRecordsHistorical (col1, col2...) 
SELECT col1, col2...
FROM tblRecordsCurrent

EDIT:

You can create a long SQL string by concatenating the string together:

SQLString = "INSERT INTO tblRecordsHistorical (col1, col2...) " & _
            " SELECT ... " & _
            " FROM tblRecordsCurrent "
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • I understand how to execute an action query within VBA, but I need to be able to specify criteria on the fly. I've created the SQL statement using the query builder, then tried to copy it to a docmd.runsql and it's too long. – aSystemOverload Jul 16 '12 at 10:35
  • Avoid DoCmd.RunSQL, it leads you into wrong doing, .Execute against a connection, but that is an aside. – Fionnuala Jul 16 '12 at 10:37
  • 1
    Have you tried concatenating the string together? SQLString = SQLString & " more SQL" [Example of strings in VBA](http://stackoverflow.com/questions/1802120/building-sql-strings-in-access-vba) – Taryn Jul 16 '12 at 10:38
  • @Remou I was just showing it as an example. thanks for pointing that out. – Taryn Jul 16 '12 at 10:39
  • Building up the SQL Statement over a few lines worked a treat, if one of you can post that as a possible answer I will accept it and UPVOTE both of your comments... – aSystemOverload Jul 16 '12 at 10:47
  • Remou, what did you mean about docmd.runsql having issues, never used any other ways to execute a statement. – aSystemOverload Jul 16 '12 at 10:49
  • @aSystemOverload With DoCmd.RunSQL you get system warnings, which you will be tempted to switch off with set warnings and this is not a good idea : http://stackoverflow.com/questions/11213892/whats-the-difference-between-docmd-setwarnings-and-currentdb-execute/11213943#11213943 – Fionnuala Jul 16 '12 at 11:02
  • @aSystemOverload please see my edit with an example of a string concatenation – Taryn Jul 16 '12 at 11:29