SQL Server Performance Forum – Threads Archive

Question about UPDATE LINKS

I’m not sure if I posted in the right section, but here goes. I have some code behind a MS Access database, it looks like this: Dim UTCOffset As Integer
UTCOffset = 4 If cmbGMSXtime.Value = "Standard Time (EST)" Then
UTCOffset = 5
End If .QueryDefs("UPDATE_LINKS").SQL = _
" UPDATE LINKS SET LINKS.ARR_TIME = [LINKS.ARR_TIME] – " & UTCOffset & "/24 " & _
" WHERE LINKS.MARK IN (1,3);"
.QueryDefs("UPDATE_LINKS").Execute .QueryDefs("UPDATE_LINKS").SQL = _
" UPDATE LINKS SET LINKS.DEP_TIME = [LINKS.DEP_TIME] – " & UTCOffset & "/24 " & _
" WHERE LINKS.MARK IN (1,4);"
.QueryDefs("UPDATE_LINKS").Execute Basically it determines the variable UTCOffset’s value from a combobox on a form. Then it proceeds using that value in writing some query statements. I think I sort of know what UPDATE does but is LINKS a keyword or a table? I couldn’t find anything called ‘LINKS’. Also what does ‘WHERE LINKS.MARK IN (1,4)’ mean? Thanks for any help. Colin

Links is the table name and it is used as an alias name in the column
You can use without alias name as there are no joins Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Evanescence Also what does ‘WHERE LINKS.MARK IN (1,4)’ mean? Thanks for any help. Colin <hr height="1" noshade id="quote"></blockquote id="quote"> Hi Colin, I think you need to read about basics of SQL language. This is the condition where table LINKS with Column MARK is being checked for values either 1 or 4. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Thanks guys. Sorry I’m a beginner to this stuff I’m still in the process of learning. I deal with mainly VBA at my job but on rare occasions I have to look at some SQL and I always have a lot of trouble. Relating to the LINKS table, I can’t seem to find it anywhere. Any ideas as to what’s happening? Thanks again.

Does your Database have LINKS table?
Did you get any error? Madhivanan Failing to plan is Planning to fail

The code works and I get the output and everything, but I can’t find the LINKS table. But here’s something that’s kinda fishy that occurs after the code in my first post: .QueryDefs("DROP_LINKS").Execute Is that table being deleted at the end? It’s kind of a long story but my main concern is actually with the UTCOffset variable. It’s either set to a value of 4 or 5 and this corresponds to whether or not Day Light Savings time is used. The problem is that whether the value is 4 or 5, the output I’m getting always shows the same times, as though the 4 or 5 is ineffective. Thus, I proceeded to examine these UPDATE statements.

quote:Originally posted by Evanescence Thanks guys. Sorry I’m a beginner to this stuff I’m still in the process of learning. I deal with mainly VBA at my job but on rare occasions I have to look at some SQL and I always have a lot of trouble. Relating to the LINKS table, I can’t seem to find it anywhere. Any ideas as to what’s happening? Thanks again.

You check for the object in Queries also below tables in left pane.
As in access Queries objects are similar to Views.

There’s a With statement that you haven’t included, which refers to either a DAO database object, which could be CurrentDb() itself, or a database object variable which is set to CurrentDb() (it is preferred to always use an object). Now if you use With <objectvariable> …… End With, then you can just write .&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ropertyname> or ![<fieldname>] in that section, by way of shorthand. Within the Database object, there are several Container objects, one of which is QueryDefs: this contains all the permanent queries inside the MDB referred to by the Database object (which could be set to CurrentDb(), or to an external MDB). There is also a TableDefs container, which contains both the tables inside the MDB, and tables to which the MDB contains permanent links (linked tables). .QueryDefs(<queryname&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />.SQL >>> sets the query statement for the permanent query object that has the given query name. .QueryDefs(<queryname&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />.Executes >>> executes the action query that is defined in the given query object. As long as you don’t set the Connect property of the selected query in QueryDefs, the query will be executed by Jet against its own TableDefs (but you can also use the [<MDBfilename>].<tablename> syntax for an ad-hoc link). If you set a Connect property, this will make a connection to an external database server, like SQL Server, and execute the query remotely – this is known as a pass-through query – pretty much like you execute queries from QA. In your MDB you should find, in the database window on the Queries tab, a query called "UPDATE_LINKS" and another one called "DROP_LINKS". The action query of the first one is set by your code, so you can see what it does. You’ll have to open the second query to see the details of the second action query. Okay, it doesn’t stop there, unfortunately … Check if there is a table called LINKS in the database window. But perhaps there is only a query called LINKS … The [LINKS.ARR_TIME] syntax can occur if you are querying an underlying query, which contains two tables, and the underlying query is selecting a column from both tables with the same name. HIH [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>