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

Access SQL statement to delete records in external table, compile error

P: 49
I'm 95% sure this will end up being a newbee question...

Here is the code. The dbo_Link_Data is a table in a MicroSoft SQL server that is linked through ODBC.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeletePrevDayRecords_Click()
  2.  
  3. Dim MyDb As Database
  4. Dim DBLink As Recordset
  5. Dim SQLStg As String
  6. Set MyDb = DBEngine.Workspaces(0).Databases(0)
  7. Set DBLink = MyDb.OpenRecordset("dbo_Link_Data", DB_OPEN_DYNASET)
  8.  
  9. DoCmd.Hourglass True
  10. Set SQLStg = "Delete * from DBLink where day(dblink.TIMESTAMP1) <> Day(Now())"
  11. DoCmd.RunSQL SQLStg
  12. DoCmd.Hourglass False
  13.  
  14. End Sub
  15.  
Error is showing up on the Set SQLStg line, line 10.
Feb 15 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Change DBLink in the SQL string to dbo_Link_Data. The SQL engine does not have access to VBA objects.
Feb 15 '12 #2

P: 49
Same error Compile error Object required. Error is saying same line.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeletePrevDayRecords_Click()
  2.  
  3. Dim MyDb As Database
  4. Dim DBLink As Recordset
  5. Dim SQLStg As String
  6. Set MyDb = DBEngine.Workspaces(0).Databases(0)
  7. Set DBLink = MyDb.OpenRecordset("dbo_Link_Data", DB_OPEN_DYNASET)
  8.  
  9. DoCmd.Hourglass True
  10. Set SQLStg = "Delete * from dbo_Link_Data where day(dblink.TIMESTAMP1) <> Day(Now())"
  11. DoCmd.RunSQL SQLStg
  12. DoCmd.Hourglass False
  13.  
  14. End Sub
  15.  
Feb 15 '12 #3

NeoPa
Expert Mod 15k+
P: 31,489
AndyB2:
I'm 95% sure this will end up being a newbee question...
You were spot on, but don't let that worry you. Many are, once you see the problem ;-)

Set is used for, and only for, assigning objects. It isn't used for the standard variable types like strings etc. So, line #10 should be simply :
Expand|Select|Wrap|Line Numbers
  1. SQLStg = "Delete * from DBLink where day(dblink.TIMESTAMP1) <> Day(Now())"
Feb 15 '12 #4

Rabbit
Expert Mod 10K+
P: 12,365
NeoPa, the poster is also referencing a VBA variable inside the SQL.

Andy, You still have a reference to the VBA variable in your modified SQL string.
Feb 15 '12 #5

NeoPa
Expert Mod 15k+
P: 31,489
Rabbit:
NeoPa, the poster is also referencing a VBA variable inside the SQL.
No they're not :-D They may think they are, but really they're not.

Ultimately, the string may not be valid SQL (and of course, you're right. It's not.), but the error reported was down to trying to assign a string to a variable using Set. The problems with the SQL would never see light of day with that still as it was. Even then it wouldn't be a VBA compile error, but it might report a runtime error when an attempt was made to pass it to Jet for processing.
Feb 16 '12 #6

P: 49
Thanks the correct code that worked is:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Hourglass True
  2. SQLStg = "Delete * from dbo_Link_Data where day([dbo_Link_Data].[TIMESTAMP1]) <> Day(Now())"
  3. DoCmd.RunSQL SQLStg
  4. DoCmd.Hourglass False
  5.  
The DBLink errored out. Had to use the dbo_Link_Data.

Thank you very much
Feb 16 '12 #7

NeoPa
Expert Mod 15k+
P: 31,489
AndyB2:
The DBLink errored out. Had to use the dbo_Link_Data.
Just as Rabbit anticipated. It wasn't the main question, but think of it as a bonus answer :-D

Here's another bonus :
Are you sure you only wish to compare the Day() value of each date? You don't want to compare the dates to see if they're actually the same?

PS. The current date is Date(). The current date/time is Now().
Feb 16 '12 #8

P: 49
I am deleting out any data that is not the current day. I think using Date() would be more correct, but because I am cleaning up the file daily Day() is working. I think I will change it to Date() just incase.
Feb 16 '12 #9

NeoPa
Expert Mod 15k+
P: 31,489
That sounds like a good idea :-)

Be sure that you're only dealing with date values though. If the data held reflects times as well (as opposed to just dates) then you may need the function DateValue().
Feb 16 '12 #10

Post your reply

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