Looking for an Alternative to INSERT

[AndyB2]

Newbee question #6:

I'm looking to see if there is a better command I could be using to copy a table out of access into a MicroSoft SQL server besides Insert?

Private Sub CopyRecordsToSQL_Click()
Dim Flag As Boolean
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set dataarea = MyDb.OpenRecordset("dbo_DATA_AREA", DB_OPEN_DYNASET)
Set SummaryData = MyDb.OpenRecordset("SupperSummary", DB_OPEN_TABLE)
Dim SQLStg As String
 
DoCmd.Hourglass True
While dataarea.Flag = False
Wend
'Set Data Area Flag
DoCmd.SetWarnings False
SQLStg = "Delete * from dbo_DATA_AREA"
DoCmd.RunSQL SQLStg
SQLStg = "Insert into dbo_DATA_AREA (Id, Inuse) VALUES (1,'False')"
DoCmd.RunSQL SQLStg
 
SQLStg = "Delete * from dbo_SupperSummary"
While Not SummaryData.EOF
    DoCmd.RunSQL SQLStg
    SQLStg = "Insert into dbo_SupperSummary (Crew, Asset, Quality, Operator_ID, StartTimeStamp1, & _"
    EndTimeStamp1, Total_time, Hour_Count, StartProduction, EndProduction, TotalProduction, DT_Cat, & _
    Dt_Code, Production_Type, Shift, Status, Downtime, Expr1, Expr2, Earned_HR_Goal, Standard_Crew, & _
    Department, WorkGroup, WorkCenter, EarnedPC, EarnedPCDesc, Location, Eff, Asset_Name) & _
    VALUES (SummaryData.Crew, SummaryData.Asset, SummaryData.Quality, SummaryData.Operator_ID, & _
    SummaryData.StartTimeStamp1, SummaryData.EndTimeStamp1, SummaryData.Total_time, & _
    SummaryData.Hour_Count, SummaryData.StartProduction, SummaryData.EndProduction, & _
    SummaryData.Total_Production, SummaryData.DT_Cat , SummaryData.Dt_Code, & _
    SummaryData.Production_Type, SummaryData.Shift, SummaryData.Status, SummaryData.Downtime, & _
    SummaryData.Expr1, SummaryData.Expr2, SummaryData.Earned_HR_Goal, SummaryData.Standard_Crew, & _
    SummaryData.Department, SummaryData.WorkGroup, SummaryData.WorkCenter, SummaryData.EarnedPC,& _
    SummaryDataEarnedPCDesc, SummaryData.Location, SummaryData.Eff, SummaryData.Asset_Name)"
    DoCmd.RunSQL SQLStg
Wend
 
SQLStg = "Delete * from dbo_DATA_AREA"
DoCmd.RunSQL SQLStg
SQLStg = "Insert into dbo_DATA_AREA (Id, Inuse) VALUES (1,'True')"
DoCmd.RunSQL SQLStg
 
DoCmd.SetWarnings True
DoCmd.Hourglass False
 
End Sub

If insert is the way I have to go could I get a hand with my syntex on ending a line with the "& _"? I'm getting errors with it.

Thanks

[NeoPa]

Andy,

This is nonsense code and shouldn't have been posted in this form. See Before Posting (VBA or SQL) Code.

SQL Server has a way to import tables in from specified Access databases. You may like to explore that avenue.

Otherwise INSERT would be the way to go. When working with SQL you need to start with an understanding of the SQL you want to end up with before you start trying to code that up in VBA. See How to Debug SQL String for some of the pitfalls you'll find there.

[ADezii]

Look into Data Transformation Services (DTS)

[AndyB2]

Thanks for the response I will:
a) Only post the part of the code I have the question with.
b) Look into DTS, but the table needs to be updated between access and the SQL server every 5 to 10 mins for live reporting. Otherwise I will stick with INSERT.
c) Once I figure out the correct way to use "& _" at the end of the line to wrap an SQL statement, I will post cleaner code as in the example Pa pointed out.

[Rabbit]

Why synchronize rather than linking to the table directly?

[NeoPa]

@Rabbit.
That is the question asked. I imagine due to the evident difficulties they're having with coding the INSERT SQL.

@Andy.
As a starter, the & _ part is VBA code and should not be included within the string values. I was hoping you may pick this up from the How to Debug SQL String article I linked you to. It's at the heart of why many coders struggle in this area, and understanding the concept for yourself is a much more satisfactory situation than my simply giving you answers. Think of the old aphorism about teaching someone to fish and they're able to feed themselves for life.

Understanding what part of the code is VBA (doing the work) and what part is a resultant SQL string that is then passed over to some SQL engine to be processed, is so critically important. Once you understand that then this type of problem simply disappears.

[AndyB2]

@ NeoPa
I 100% agree with you on formating. My question is in VBA how do I enter a SQLStg = "Insert ect....." (example below) on multible lines and have it still be valid code?

SQLStg = "Insert into dbo_SupperSummary (Crew, Asset, Quality, Operator_ID, ect.....

Or were you talking formating for posting a question only? Which I can understand also. I am just hoping I can keep that formating in my code.

[NeoPa]

AndyB2:
My question is in VBA how do I enter a SQLStg = "Insert ect....." (example below) on multible lines and have it still be valid code?

I thought I'd already explained this Andy, but maybe you missed this from post #6.

NeoPa:
As a starter, the & _ part is VBA code and should not be included within the string values.

Maybe it would help here if I included an illustration of some working VBA code that sets up a SQL string and you can better understand what I'm talking about :

strSQL = "SELECT [X]" & _
         "     , [Y]" & _
         "     , [Z] " & _
         "FROM   [Table]"

Notice that line #3 ends the string (within the quotes - ") with a space. Probably the most frequent mistake is to forget the white-space within the string, because the formatting of the VBA code makes it appear unnecessary.

The resultant string can be seen here :

"SELECT [X]     , [Y]     , [Z] FROM   [Table]"

[AndyB2]

Thank you very much, the light bulb just went on. Now I'm not sure how much light I'm putting off........

[NeoPa]

AndyB2:
Or were you talking formating for posting a question only?

I forgot to respond to this earlier :-(
In this instance, No. But I have been known to ;-), so it was worth asking.

PS Glad that's starting to clarify. It can all get a bit murky, to be sure. Especially when you first start.