What is wrong with my Access strQuery

I have an Access table that I am trying to append records.
It has the following structure
Access tableI have read in the values for the variables Action, Person, myDate, Comment, Outcome and Acttno from an Excel table with values of
"Phone Call", "Huw", #14/12/2017#, "blank", "blank", "Ac TEGY-01 006"
but when I try to update the records it fails on the  execute line with message "no value given for one or more parameters"

 
strQuery = "INSERT INTO Actions ([Action], [Person],[Action Date],[Comment],[Outcome],[Act No]) " & _
            "VALUES (Action, Person, mydate, Comment, Outcome,Acctno);"
               

        cn.Execute strQuery

Open in new window


What am I doing wrong?
LVL 1
rogergovierPartnerAsked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
good catch, Gustav ~

hi Roger,

> "Didn't think you'd see the day when I would be asking a question about Access - did you!!!"

glad I saw your question, and that I get to help steer you on the right path ;) Nice to see you too!

> " then delete them when the actions have been completed ... Master file will then use Get Data to pull in all the info from the Access table"
Perhaps consider archiving historical records instead of deleting? Or marking dtmXfer (if not blank, then ignore) with the date/time transferred back to the Master file? Or store XferID instead of dtmXfer? XferID  would be the primary key in a new table in Access called Transfers that records information about what is imported and exported such as where to, where from, date/time, how many records, how many values, etc.

You might notice a little crazy naming ... like XFerID instead of TransferID. I like to keep fieldnames short when they are over short data --- what will the biggest AutoNumber be? 999 (3 characters)? 9,999 (5 characters)? Actually, "TransferID" is really not that long in comparison to what other fields will be so maybe better not to be so cryptic for a newbie like you <smile> I just usually use 'xfer' myself to mean import/export --  my own little idiosyncisy to recognize it faster.

Short fieldnames are nice when trying to squeeze a bunch of tables and show all the fields of each on the Relationships Diagram too.  Save the wordy descriptive stuff for, well, the Description ~

Maybe Access could track the progress of actions? then when one action is similar to another in the past, notes, times, and who did what might be useful.

Access is a great way to consolidate all data into one place and get historical reports, and reports that summarize lots of worksheets and workbooks too.  Maybe you should learn a bit more than what you needed for this? I know a few more links ... ~ yes, Access is a pretty nifty Excel Add-in ;) ... remember that time around the table at karaoke? were you the one who wrote that on my badge?

> "Are you saying that there is a way of grabbing the data direct from an Excel table?"

yes!

To see how this works:

1. create a range in Excel with the name: DataExport
2. make it be one or however many rows of data you want Access to transfer for the test.
3. save and close the workbook
(optional:)
4. Alt-F11 to go to VBA
5. Ctrl-G for debug window
6. paste:
?ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
and press Enter.
7. Copy it.

In Access:
1. EXTERNAL DATA ribbon
2. Excel icon in the Import & Link group
3. a dialog box pops up to specify file and link type
      in the File name box, you can paste the path and filename if you have it, or click Browse to locate the Excel file
      click option to "Link to the data source by creating a linked table."
      click OK
4. the next dialog box asks what data you want
      click the option to "Show Named Ranges"
            (instead of Show Worksheets)
      choose the DataExport range -- or whatever you called it
      click Next
5. "First Row Contains Column Headings" is probably not checked ... nor should it be in this case -- but here it is for the future ;)
      click Next
6. choose a name for the table.  Perhaps you want to add "import_" to the beginning of what is there? or change it however you like (for instance: import_myNewRows).  Good idea not to use spaces or special characters in names (underscore _ is ok).
      click Finish

you will now see a linked table.

make the query you need -- save it, grab its SQL, and whatever

now that you know the manual process, you can build code to open Excel, link to whatever the latest data is programatically

since the first row did not have fieldnames, Access creates fieldnames such as F1, F2, F3, ...

now that you have a table, you can make an Append query to transfer the records that are in Excel.

~~~
go to the Immediate (debug) window (in Access, you can press Ctrl-G from anywhere, not just code).  See what you get for these:

?currentdb.TableDefs("import_myNewRows").Connect

?currentdb.TableDefs("import_myNewRows").SourceTableName

?currentdb.TableDefs("import_myNewRows").Name

The Name is what shows up in the Navigation Pane.  The Source Table Name will be the range name (as opposed to MySheetname$ or Sheet1$a1:d4). Notice $ is used after a sheetname, not !

so, if you knew the address in Excel where the data to import is (which of course you do!), you can change the SourceTableName and import_myNewRows will now have different data!  ... however, you cannot simply change that property -- you will need to destroy the linked table and recreate it, which can be done with VBA

~~~
btw, just like "Master", "myNewRows" is really not descriptive  ... maybe something like import_NewTablename where Tablename is the name of the table they are new rows for ... or import_master ... well, give it some thought ;) ~

have an awesome day,
crystal
0
 
Pawan KumarDatabase ExpertCommented:
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'
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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:
Append Query
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:
make Append Query
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
Hi,

Also, keep in mind that the SQL engine have no clue what VBA variables are.
You should concatenate your variables values into your SQL statement.
strQuery = "INSERT INTO Actions ([Action], [Person],[Action Date],[Comment],[Outcome],[Act No]) " & _
            "VALUES (""" & Action & """, """ & Person & """, """ & mydate & """, """ & Comment & """, """ & Outcome & """, """ & Acctno & """);"

Open in new window

Or better, use a parameterized query (prevent SQL injection and issues with quotes and double quotes in variable's values:
strQuery = "PARAMETERS paramAction TEXT(255), paramPerson TEXT(255), parammydate TEXT(255), paramComment TEXT(255), paramOutcome TEXT(255), paramAcctno TEXT(255);" & _
           "INSERT INTO Actions ([Action], [Person],[Action Date],[Comment],[Outcome],[Act No]) " & _
           "VALUES ([paramAction], [paramPerson], [parammydate], [paramComment], [paramOutcome], [paramAcctno]);"
Dim db As DAO.Database
Set db = CurrentDb
Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef("tempQD", strsql)
qd.Parameters("paramAction").value = Action;
qd.Parameters("paramPerson").value = Person;
qd.Parameters("parammydate").value = mydate;
qd.Parameters("paramComment").value = Comment;
qd.Parameters("paramOutcome").value = Outcome;
qd.Parameters("paramAcctno").value = Acctno;
qd.execute(dbFailOnError)
qd.close
db.QueryDefs.Delete qd.Name
Set qd = Nothing
set db = Nothing

Open in new window

0
 
rogergovierPartnerAuthor Commented:
@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.
0
 
rogergovierPartnerAuthor Commented:
@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.
0
 
rogergovierPartnerAuthor Commented:
@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.
0
 
Gustav BrockCIOCommented:
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") & "#," & ...

Open in new window

In case of parameters, your SQL should be like:

PARAMETERS ActionDate DateTime, ... ;

Open in new window

/gustav
1
 
rogergovierPartnerAuthor Commented:
@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?
0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
hi Roger,

you're most welcome

> "for times sake (I have to have this completed by Wednesday!!) I will go with the method that has worked thus far"

glad you got it working! After the process,  be sure to spot-check the data, especially dates to ensure they are correct ~ and use what Gustav told you about date handling ALL the time!!! ... as you are in one of those goofy places that does dates different ;) -- actually, your way makes better sense.  I like yyyy-mm-dd the best ... and, aside from the standard American format, Access will always get this right.

> "maybe one day I will do some more experimenting along those lines"

so save this thread to come back and read later when you have time to get a more solid understanding of Access? ... and read my Access Basics book too (it is mostly concepts, so version-independent) ...  I plan to quiz you ;) ~

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

what information on the record is unique that could be used to identify it?

how will you know if a record needs to be deleted? Maybe the XLSX file could be an XLSM? So when a row is deleted in the Master file, it goes and deletes the Access record first?

Is what is in Access exactly what is in Excel? If yes, then instead of appending new rows and then deleting them later (what about changed rows?) ... why not just link to whatever is in Excel at the time?  (is there a problem sharing via Access too? ...  Access links to Excel as read-only). If no, what is different?

What does the Access application (front-end) do?

If information is collected in Access, what happens to it?

Do you have multiple users in Access? ... yes, right? If so, are there queries, forms, reports, macros, or modules that people share? If so, the back-end (tables) should be in their own database and the user interface (UI - forms, reports, queries, macros, modules) should be in a separate database that only one person opens at a time.  If more than one user needs to use the application at the same time, then each user should have their own copy of the FE; an exception to this might be made if each user is read-only  -- but typically, that is not the case.  Generally, a front-end (FE) will have only linked tables to the back-end (BE) or back-ends. Of course, there may be a sprinkle of local tables too, that are not shared.

I am going to hop off now ... have an awesome day,
crystal
1
 
Pawan KumarDatabase ExpertCommented:
Very nice explanation crystal.
1
 
rogergovierPartnerAuthor Commented:
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

Open in new window


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

Regards
Roger Govier
0
 
Gustav BrockCIOCommented:
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
0
 
rogergovierPartnerAuthor Commented:
@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.
0
 
Gustav BrockCIOCommented:
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
2
 
rogergovierPartnerAuthor Commented:
@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
0
 
Gustav BrockCIOCommented:
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
1
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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]
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.