Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-11-2019, 11:18 PM   #1
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Issues appending records with dates into SQL Server

I have a sub inside an unbound form that appends a record to a table in SQL server using a pass-through query. The sub creates the necessary SQL then runs a Querydef. One of the fields is a date field. The function runs without error and inserts a record, but the date field is blank. However, if I cut and paste the SQL directly into SSMS it also inerts a record but with a date. I am confused. the code, & SQL is below.

Code:
    Dim strID As String: strID = GetNextIndex("IR", "tblItemRequest", "IR_ID")
    Dim db As Database
    Dim qdf As dao.QueryDef
    Dim strSQL As String
    
    strSQL = "INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) "
    strSQL = strSQL & "VALUES ("
    strSQL = strSQL & "'" & strID & "', '" & Me.txtPtNo & "', '" & Gbl_StaffNo & "', '" & sqlDate(Date) & "', " & txtQtyRequested & ", '" & Me.txtReason & "')"

    Debug.Print strSQL
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = conConnectionStores
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute

    db.Close
    Set qdf = Nothing
    Set db = Nothing

    MsgBox "Added"
The SQL that results from variable strSQL is below:

Code:
INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) VALUES ('IR00000007', 'I_000012', '59899', '20191212', 1, 'test entry')
NB the function sqlDate takes a date argument and returns the date as a string in format YYYYMMDD.

I know I could have a get around by setting the date field in SQL with a default date of now, but I will need to use the same principle with dates other than today. So I would like to understand what is going wrong.

Zedster is offline   Reply With Quote
Old 12-12-2019, 01:19 AM   #2
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Re: Issues appending records with dates into SQL Server

This is giving me a real headache. I tried changing strSQL to use CAST as follows:

Code:
    strSQL = "INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes],[IR_Status]) "
    strSQL = strSQL & "VALUES ("
    strSQL = strSQL & "'" & strID & "', '" & Me.txtPtNo & "', '" & Gbl_StaffNo & "', CAST(" & sqlDateTime(Now) & " as datetime), " & txtQtyRequested & ", '" & Me.txtReason & "', 'Reserved')"
debug.print of strSQL gives:

Code:
INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes],[IR_Status]) VALUES ('IR00000011', 'I_000019', '59899', CAST('20191212 09:12:02' as datetime), 2, 'another test', 'Reserved')
When I run the VBA subroutine it fails to run with the following error: "3075 Syntax error (missing operator) in query expression 'CAST('20191212 09:12:02' as datetime)'

Yet again, if i cut and paste this SQL into SSMS it inserts the record fine!
Zedster is offline   Reply With Quote
Old 12-12-2019, 07:39 AM   #3
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,496
Thanks: 12
Thanked 2,305 Times in 2,256 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Issues appending records with dates into SQL Server

I believe yoyr query is running in the Jet SQL engine, not the SQL Server engine. That means your statement must work in Access, not SQL Server. Which means you should use CDate(https://www.techonthenet.com/access/...type/cdate.php) not CAST().

plog is offline   Reply With Quote
Old 12-12-2019, 10:36 AM   #4
nhorton79
Newly Registered User
 
Join Date: Aug 2015
Posts: 129
Thanks: 16
Thanked 18 Times in 18 Posts
nhorton79 is on a distinguished road
Re: Issues appending records with dates into SQL Server

I donít really know a lot about SQL Server but Iíve had hassles in Access using dates when Iíve forgotten to add hashes.

I.e.
Code:
...#Ē & myDateField & ď#...

Sent from my iPhone using Tapatalk
nhorton79 is offline   Reply With Quote
Old 12-12-2019, 10:48 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,491
Thanks: 15
Thanked 4,148 Times in 4,079 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Issues appending records with dates into SQL Server

I'm not convinced you're actually creating a pass through query with that code. Personally I create a pass through query manually and save it. Then in code I just change its SQL property and execute/open it as appropriate.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-12-2019, 11:04 AM   #6
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,415
Thanks: 0
Thanked 805 Times in 790 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Issues appending records with dates into SQL Server

Try formatting your date "sql server" style YYYY-MM-DD
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
The Following User Says Thank You to namliam For This Useful Post:
Zedster (12-12-2019)
Old 12-12-2019, 11:06 AM   #7
nhorton79
Newly Registered User
 
Join Date: Aug 2015
Posts: 129
Thanks: 16
Thanked 18 Times in 18 Posts
nhorton79 is on a distinguished road
Re: Issues appending records with dates into SQL Server

I also found this on SO which talks about using parameters:

http://<br /> <br /> https://stackov...a<br /> <br />

A comment by Dai further down talks about this in further detail...


Sent from my iPhone using Tapatalk

nhorton79 is offline   Reply With Quote
Old 12-12-2019, 11:16 PM   #8
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by plog View Post
I believe yoyr query is running in the Jet SQL engine, not the SQL Server engine. That means your statement must work in Access, not SQL Server. Which means you should use CDate(https://www.techonthenet.com/access/...type/cdate.php) not CAST().
Confused. If the query isn't using the SQL server engine, why is the record being added to the table on SQLServer. See first paragraph of the post "..The function runs without error and inserts a record, but the date field is blank...".
Zedster is offline   Reply With Quote
Old 12-12-2019, 11:18 PM   #9
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by pbaldy View Post
I'm not convinced you're actually creating a pass through query with that code.
What draws you to that conclusion?
Zedster is offline   Reply With Quote
Old 12-12-2019, 11:37 PM   #10
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by namliam View Post
Try formatting your date "sql server" style YYYY-MM-DD
Thank you, you solved it while I still have hair left! Access now executes the query on the SQLServer, adds the record and populates the date field, perfect!

Though I still don't understand why all the other attempts ran fine on the SQL server when I run the SQL code directly on SSMS by pasting the results of "debug.print strSQL". Yet fail to add the date when called from access.
Zedster is offline   Reply With Quote
The Following User Says Thank You to Zedster For This Useful Post:
nhorton79 (12-13-2019)
Old 12-13-2019, 01:12 AM   #11
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,415
Thanks: 0
Thanked 805 Times in 790 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Issues appending records with dates into SQL Server

Because you dont run any query ON THE DATABASE, you run it in SSMS or in any other frontend which "help" you if they can... thus hiding these little problems from you.
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 12-13-2019, 09:49 AM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,491
Thanks: 15
Thanked 4,148 Times in 4,079 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by Zedster View Post
What draws you to that conclusion?
The fact that the same SQL in SSMS works as expected. I assume you didn't try with a saved pass through query?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-16-2019, 12:42 AM   #13
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by pbaldy View Post
I assume you didn't try with a saved pass through query?
Your assumption is right. So to experiment I have just tried creating two saved pass through queries based on the string created by strSQL:

Code:
INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) VALUES ('IR00000007', 'I_000012', '59899', '20191212', 1, 'test entry')
Code:
INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes],[IR_Status]) VALUES ('IR00000011', 'I_000019', '59899', CAST('20191212 09:12:02' as datetime), 2, 'another test', 'Reserved')
Both run fine and insert records with dates on the SQL server.

So I am more confused than ever. The VBA code below runs on the SQL server and inserts the new record (without the date) which is visible in SSMS. Yet the same SQL string when created as a saved pass through query does insert the date.

I would love to know what the issue is. When you say "..I'm not convinced you're actually creating a pass through query with that code.." if it isn't a passthrough query how is it inserting the record in the SQL Server and what do I need to do to get it to become a passthrough query. Incidentally the constant conConnectionStores is just a constant for the connection string: "ODBC;DRIVER=SQL Server;SERVER=TEST-LT-1812\SQLDEVELOPER;DATABASE=Stores;Trusted_Connecti on=Yes"

Code:
    Dim strID As String: strID = GetNextIndex("IR", "tblItemRequest", "IR_ID")
    Dim db As Database
    Dim qdf As dao.QueryDef
    Dim strSQL As String
    
    strSQL = "INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) "
    strSQL = strSQL & "VALUES ("
    strSQL = strSQL & "'" & strID & "', '" & Me.txtPtNo & "', '" & Gbl_StaffNo & "', '" & sqlDate(Date) & "', " & txtQtyRequested & ", '" & Me.txtReason & "')"

    Debug.Print strSQL
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = conConnectionStores
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute
Zedster is offline   Reply With Quote
Old 12-16-2019, 08:44 AM   #14
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,491
Thanks: 15
Thanked 4,148 Times in 4,079 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by Zedster View Post
I would love to know what the issue is. When you say "..I'm not convinced you're actually creating a pass through query with that code.." if it isn't a passthrough query how is it inserting the record in the SQL Server and what do I need to do to get it to become a passthrough query.
Presuming you have a linked table by that name it could be creating a native Access query and inserting via the linked table. That seems more likely if the SQL that fails in your code works with a saved pass through.

To be honest I'm not sure what you're creating. I've never tried to create a pass through with code and a brief search didn't turn up code that did. I use saved pass through queries and a function similar to the "PassThroughFixup" function here:

https://www.sqlservercentral.com/for...-to-sql-server

which I pass the SQL and any other appropriate arguments to prior to using the query.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-17-2019, 12:10 AM   #15
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 56
Thanks: 23
Thanked 1 Time in 1 Post
Zedster is on a distinguished road
Re: Issues appending records with dates into SQL Server

Quote:
Originally Posted by pbaldy View Post
Presuming you have a linked table by that name it could be creating a native Access query and inserting via the linked table. That seems more likely if the SQL that fails in your code works with a saved pass through.
I did a bit of experimenting and it would appear you are right. I have linked tables in the database with the same name as those on the SQL server which have been masking what is going on. I was presuming the queries I had created in VBA were working as pass through queries directly on the SQL server. However, if I delete the linked table the code fails to execute the query because it can't find the table, so it has been linking to the local table all the time, hence why the code wasn't executing because it was written in SQL Server SQL rather than Access SQL. It looks like I need to find out why the code isn't creating pass through queries and if I can solve that then the queries should work.

Zedster is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Installation Issues Zedster SQL Server 4 09-05-2019 08:14 AM
Appending Data from SQL Server into Access Geoff Codd Modules & VBA 3 09-07-2006 07:18 PM
Upsizing Issues, Access to SQL Server gvcooper SQL Server 1 04-30-2006 06:02 PM
Performance issues when changed server IanW General 1 11-06-2002 05:14 AM
Project- Server connection issues VBAhole22 General 0 10-09-2002 01:14 PM




All times are GMT -8. The time now is 07:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World