By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

Looking for an Alternative to INSERT

P: 49
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyRecordsToSQL_Click()
  2. Dim Flag As Boolean
  3. Set MyDb = DBEngine.Workspaces(0).Databases(0)
  4. Set dataarea = MyDb.OpenRecordset("dbo_DATA_AREA", DB_OPEN_DYNASET)
  5. Set SummaryData = MyDb.OpenRecordset("SupperSummary", DB_OPEN_TABLE)
  6. Dim SQLStg As String
  7.  
  8. DoCmd.Hourglass True
  9. While dataarea.Flag = False
  10. Wend
  11. 'Set Data Area Flag
  12. DoCmd.SetWarnings False
  13. SQLStg = "Delete * from dbo_DATA_AREA"
  14. DoCmd.RunSQL SQLStg
  15. SQLStg = "Insert into dbo_DATA_AREA (Id, Inuse) VALUES (1,'False')"
  16. DoCmd.RunSQL SQLStg
  17.  
  18. SQLStg = "Delete * from dbo_SupperSummary"
  19. While Not SummaryData.EOF
  20.     DoCmd.RunSQL SQLStg
  21.     SQLStg = "Insert into dbo_SupperSummary (Crew, Asset, Quality, Operator_ID, StartTimeStamp1, & _"
  22.     EndTimeStamp1, Total_time, Hour_Count, StartProduction, EndProduction, TotalProduction, DT_Cat, & _
  23.     Dt_Code, Production_Type, Shift, Status, Downtime, Expr1, Expr2, Earned_HR_Goal, Standard_Crew, & _
  24.     Department, WorkGroup, WorkCenter, EarnedPC, EarnedPCDesc, Location, Eff, Asset_Name) & _
  25.     VALUES (SummaryData.Crew, SummaryData.Asset, SummaryData.Quality, SummaryData.Operator_ID, & _
  26.     SummaryData.StartTimeStamp1, SummaryData.EndTimeStamp1, SummaryData.Total_time, & _
  27.     SummaryData.Hour_Count, SummaryData.StartProduction, SummaryData.EndProduction, & _
  28.     SummaryData.Total_Production, SummaryData.DT_Cat , SummaryData.Dt_Code, & _
  29.     SummaryData.Production_Type, SummaryData.Shift, SummaryData.Status, SummaryData.Downtime, & _
  30.     SummaryData.Expr1, SummaryData.Expr2, SummaryData.Earned_HR_Goal, SummaryData.Standard_Crew, & _
  31.     SummaryData.Department, SummaryData.WorkGroup, SummaryData.WorkCenter, SummaryData.EarnedPC,& _
  32.     SummaryDataEarnedPCDesc, SummaryData.Location, SummaryData.Eff, SummaryData.Asset_Name)"
  33.     DoCmd.RunSQL SQLStg
  34. Wend
  35.  
  36. SQLStg = "Delete * from dbo_DATA_AREA"
  37. DoCmd.RunSQL SQLStg
  38. SQLStg = "Insert into dbo_DATA_AREA (Id, Inuse) VALUES (1,'True')"
  39. DoCmd.RunSQL SQLStg
  40.  
  41. DoCmd.SetWarnings True
  42. DoCmd.Hourglass False
  43.  
  44. 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
Feb 20 '12 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,495
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.
Feb 20 '12 #2

ADezii
Expert 5K+
P: 8,638
Look into Data Transformation Services (DTS)
Feb 20 '12 #3

P: 49
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.
Feb 21 '12 #4

Rabbit
Expert Mod 10K+
P: 12,366
Why synchronize rather than linking to the table directly?
Feb 21 '12 #5

NeoPa
Expert Mod 15k+
P: 31,495
@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.
Feb 21 '12 #6

P: 49
@ 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.
Feb 23 '12 #7

NeoPa
Expert Mod 15k+
P: 31,495
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT [X]" & _
  2.          "     , [Y]" & _
  3.          "     , [Z] " & _
  4.          "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 :
Expand|Select|Wrap|Line Numbers
  1. "SELECT [X]     , [Y]     , [Z] FROM   [Table]"
Feb 23 '12 #8

P: 49
Thank you very much, the light bulb just went on. Now I'm not sure how much light I'm putting off........
Feb 23 '12 #9

NeoPa
Expert Mod 15k+
P: 31,495
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.
Feb 23 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.