What is wrong with my Access strQuery

The query you have given it to INSERT the rows... Do you want to update the rows. then you need to use the UPDATE COMMAND.

Update TableName
SET colName = 'Value'
WHERE othercolumn = 'SomeValue'

hi Roger!

Insert Into using Values is for appending a record when you know the values for the record (since there is no FROM clause).  Perhaps you want to append from one table to another? Is the source a table linked to data in Excel?

Easiest is to make a query using the designer then copy the SQL statement into VBA code then modify it. Try:

strQuery = "INSERT INTO Actions ([Action], [Person],[Action Date],[Comment],[Outcome],[Act No]) "  _
           &  "VALUES (""Phone Call"", ""Huw"", #14/12/2017#, ""blank"", ""blank"", ""Ac TEGY-01 006"") ;"

here is what the query designer would look like:

because there is no FROM clause, no tables are displayed in the top pane. Access automatically assigns field names for each expression (expr1, expr2, etc).  These are not necessary to include in the SQL statement.

You can change the query type by right-clicking in a gray area, then choosing Query Type > Append Query ... -- then choosing the table to append to in the dialog box:

here is a short book on Access you can read:

Access Basics
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

have an awesome day,
crystal

@Crystal

Hi there, nice to hear from you.
Didn't think you'd see the day when I would be asking a question about Access - did you!!!
Yes, you're right, what I have is data in an Excel Table, that I want to append to a table in Access.
Basically, users will be dealing with a specific client files in a specific folder and updating information  abou the client there.
I want some of that information, about the last action created, to arrive in an Excel file which is collating data from all clients, so that the Admin can see what actions are outstanding from all users for all clients etc.

Clearly I can't write directly to the Master file in Excel, as there may not be R/W access available to the individual user when they are creating the source transaction in the client file.

In the past I have got round this by writing a small csv file to the folder where the Master file resides, and having an Update button which reads all the csv data in and  then Kills the csv files.

However, this client wants to use Excel and is on Sharepoint, and that doesn't play nicely with csv's. Equally I can't use the new collaboration and co-auth features of Excel in Sharepoint, as it only supports .xlsx files.

Hence the need to turn to Access. I want to append records to the Access file when actions are created, and then delete them when the actions have been completed. The Master file will then use Get Data to pull in all the info from the Access table.

Are you saying that there is a way of grabbing the data direct from an Excel table?
If so that would be great and I would appreciate a pointer as to how.
Otherwise it sounds like I have to create the stQuery with =""" & value1 & ,""" & """ & value2 & ,""" etc., which is a bit of a pain, bt if that's what it takes then so be it.

Look forward to hearing back and look forward to seeing you in Redmond in March.

@Fabrice

Many thanks to you for this.
If you look at my reply to Crystal (whom I know), then you will see that at the end this was the conclusion I had come to unless there is a way to talk straight tot he Excel Table.

@Crystal and @ Fabrice
Just to let you know, appending all of the double quotes and commas worked fine and the records have been appended as required.

Now I  have to sort out now selecting relevant record, by Act No to delete when the action has been completed.
Hopefully that won't cause me any problems, else I'll be back.

Your date value should not be quoted but wrapped in octothorpes - and not like #14/12/2017# but as #2017/12/14#:

... & ",#" & Format(YourDateVValue, "yyyy\/mm\/dd") & "#," & ...

Select allOpen in new window

In case of parameters, your SQL should be like:

PARAMETERS ActionDate DateTime, ... ;

Select allOpen in new window

/gustav

@Gustav

Many thanks.
I had realised that and enclosed my dates within #     # and not withing quotes.

@Crystal

Thank you ever so much for this. I am extremely grateful.
That looks fascinating, and maybe one day I will do some more experimenting along those lines.
However, I think for times sake (I have to have this completed by Wednesday!!) I will go with the method that has worked thus far, as everything will then be the same within each Client Excel file, with the same VBA code generating the strQuery and all the data going to the one Access table.

Within the Excel files, the tables have the action info and in the same record the completion of action info, and that's just fine.
All I need in the Master file are any actions that have not been completed, so when the user creates an action, I am writing the data out as described with a unique action number (Act No).
When the user completes the action in the client file, then instead of creating any new record in the Access table, I just want to delete that unique record from the Table, so the Admin only has a list of actions that have not been completed to chase up.

What would be the easiest and quickest way to generate the code to just find and delete that record?

Very nice explanation crystal.

Can I thank everyone for their contributions here.
With your help and guidance, I think I have achieved what I wanted.

Basically, when I run the code from within Excel, I have a flag on each of my Excel records which is either "a" for a new Action, or "c" for a Completed action.
Then I make up a different strQuery in each case to pass to Execute.
Seems to work well for me

On Error Resume Next
        strQuery = ""
        If LCase(Exp) = "a" Then
        strQuery = "INSERT INTO Actions ([Action], [Person],[Action Date],[Comment],[Outcome],[Act No]) " & _
            "VALUES (""" & Action & """, """ & Person & """, """ & myDate & """, """ & Comment & """, """ & Outcome & """, """ & Actno & """);"
        ElseIf LCase(Exp) = "c" Then
        strQuery = "DELETE FROM Actions WHERE ([Act No] = """ & Actno & """);"
         End If
         If strQuery <> "" Then
        cn.Execute strQuery
       End If

Select allOpen in new window

Once again thank you all and I will ow mark this question as Closed.

Regards
Roger Govier

You haven't fixed your date field. Either it is still Text, which it shouldn't be, or your syntax is - as previously noted - incorrect.

/gustav

@Gustav

It is fine Gustav
When I read the data into the variable myDate, it is coming in as
  : myDate : #14/12/2017#
so I am ending up with a date in my resulting table as I want it.

You miss the point. When to take these unneeded steps, first #14/12/2017# is casted to a string, 14/12/2017, to be concatenated with the SQL.
Then this will pass "14/12/2017" to the table which will have to cast it back to a date value. This succeeds, but only because it cannot be read as any other date than 2017-12-14.

When you reach 2018-01-02 it will fail, because #02/01/2018# will be casted to 02/01/2017 which, when passed to the table as "02/01/2018" will be stored as 2018-02-01.

To repeat: Always handle date and time as Date, not text, not numbers, no exceptions.

/gustav

@Gustav

When it comes to Excel I am fairly competent, (well at least Microsoft must think so as I have been awarded MVP status for the past 11 years), but when it comes to Access I am a pure novice.
Far be it for me, therefore, to question your wisdom, but for some reason in my case what you say, does not happen.

In Excel the table I want to Export looks like this
Action      Person      Action Date      Comment      Outcome      Act No
Phone call      Huw      14 Dec 2017                  Ac TEGY-01 006
Email      Lorraine      11 Dec 2017                  Ac TEGY-01 007
Phone call      Huw      01 Feb 2018                  Ac TEGY-01 008
Email      Lorraine      02 Jan 2018                  Ac TEGY-01 009


Within VBA I dim myDate as Date, so VBA expects a Date and not a Text string.
VBA reads from my Excel table 14 Dec 2017 (because that is the way the date serial number has been formatted), as #14/12/2017#
When I pass that to Access via the code as shown I get in the Access table the following

ID      Action      Person      Action Date      Comment      Outcome      Act No
22      Phone call      Huw      14 Dec 2017                  Ac TEGY-01 006
23      Email      Lorraine      11 Dec 2017                  Ac TEGY-01 007
24      Phone call      Huw      01 Feb 2018                  Ac TEGY-01 008
25      Email      Lorraine      02 Jan 2018                  Ac TEGY-01 009

Now I can't answer for what Access  or SQL is doing along the way, but as far as I can tell I end up with exactly the data I want to see.

Regards
Roger

I ran a small test here because it is years ago I ran into the issue.
It seems like you are right; the cast to string and back will use the local setting for the date format, so your stored dates will turn out as intended. At least they do for me.

However, in general, I wouldn't count on the method.

/gustav

you're welcome, Roger ~ happy to help

as Gustav said, dates do need special handling (since you use a non-American format) -- it is a good idea to be in the habit all the time even if there are certain cases where extra caution was not needed

~~~

[thanks, Pawan]