VB5 SQL Insert into syntax - Solution and strange behavour

VB5 SQL Insert into syntax - Solution and strange behavour

Post by Brian Camer » Mon, 14 Sep 1998 04:00:00



Dear all,

Thanks for those who replied I appreciate your help, I have managed to get
the following code working, so Please feel free to use it if your after
the same thing.

However I found one very strange thing and Id like to see if other people
can duplicate it.

There seems to be something specific with using a column name of
TimeStamp
with a  MS Access 97 database??.

The table UserLog has four fields

        UserID          Text 10
        Login           Date/Time
        TimeStamp               Date/Time
        Counter         AutoNumber

But I can only get the following code to work if I rename the TimeStamp
column
to a different name?????? so with a table structure of:

        UserID          Text 10
        Login           Date/Time
        LastTimeStamp   Date/Time
        Counter         AutoNumber

It works fine.

Why??? Have I discovered a Access (dare I say it...) BUG or if by design
why and why does
the insert sintax need to be different based on the Column NAME.

My Code is

    Dim DateTme as variant

    DateTime = Format(Now, "mm/dd/yyyy h:mm:ss")

    SQL$ = "INSERT INTO Current_Users "
    SQL$ = SQL$ & "(UserID, Login, LastTimeStamp) "
    SQL$ = SQL$ & "VALUES "
    SQL$ = SQL$ & "('" & UserId & "', #" & DateTime & "#, #" & DateTime &
"#)"

    'RetnMsg = "LU: " & LicensedUsers & " j%: " & j% & " SQL$: " & SQL$
' testing only
    dbase.Execute SQL$, dbFailOnError
    DBEngine.Idle dbFreeLocks
    dbase.Close
    Set dbase = Nothing
    DoEvents
    Exit Function

Please help

TIA

Brian  the confused

 
 
 

1. VB5 SQL Insert into syntax - Solution and strange behavour

TIMESTAMP is a reserved word in Access SQL: it's a synonym for DATETIME.
Trying to use it as, say, a column name is not unlike trying to use
"Dim" as a variable name in VB. The language reserves certain words for
its own use, primarily so it can have a decent chance at correctly
parsing statements.

2. pointers (variable substitution)

3. Strange behavour of table.BeforeClose event

4. Size of an account ??

5. VB5 SQL Insert into syntax - Help Pleasessssss

6. Error while Posting data

7. Strange problem with appendchunk, stranger solution yet

8. Changing Licensing keys

9. strange ODBC SQL syntax

10. Embedded SQL/C Preprocessor - strange syntax errors

11. "SQL" syntax problem (VB5)

12. VB5 SQL Syntax Question

13. Error trying to use INSERT...EXEC syntax to insert values into a timestamped table