CODEWITHSUNDEEP
ms-accessvbams-access-2010
dmorgan20
dmorgan20

Reputation: 363

SQL Inserting in to an external table

I am looking to insert data into a different table that users are using.

I think I am on the right track to piecing this together but looks like ill need a hand

My Code:

CurrentDb.Execute "INSERT INTO [MS Access;pwd=" & strPassword & ";database=" & DBpath & "\" & DBname & "].[" & tblengagement & "]" _
        & "(CDP,Open_Date, Open_Time) VALUES " _
        & "'" & Environ("UserName") & "', Date(), Time());"

Error Syntax error in INSERT INTO statement

What I am trying to achieve When a user opens the database I want to send the username, date, time to a database stored in a different location

UPDATE

I have broken the code down and got the below working, just need to work out how to get date, time in there:

CurrentDb.Execute " INSERT INTO [MS Access;pwd=" & strPassword & ";database=" & DBpath & "\" & DBname & "].[" & tblengagement & "] " _
        & "(CDP) VALUES " _
        & "('" & Environ("UserName") & "');"

Upvotes: 0

Views: 449

Answers (1)

LiamH
LiamH

Reputation: 1502

date() and time() are functions and should not be within the quotation string. Dates and Times need to be enclosed by # signs.

CurrentDb.Execute "INSERT INTO [MS Access;pwd=" & strPassword & ";database=" & DBpath & "\" & DBname & "].[" & tblengagement & "]" _
    & "(CDP,Open_Date, Open_Time) VALUES " _
    & "'" & Environ("UserName") & "',#" & Date() & "#,#" & Time() & "#);"

Upvotes: 1

Related Questions