Issues appending records with dates into SQL Server (1 Viewer)

Zedster

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
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

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
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!
 

nhorton79

Registered User.
Local time
Today, 22:33
Joined
Aug 17, 2015
Messages
147
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:33
Joined
Aug 30, 2003
Messages
36,118
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.
 

namliam

The Mailman - AWF VIP
Local time
Today, 10:33
Joined
Aug 11, 2003
Messages
11,696
Try formatting your date "sql server" style YYYY-MM-DD
 

Zedster

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
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/functions/datatype/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

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
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.
 

namliam

The Mailman - AWF VIP
Local time
Today, 10:33
Joined
Aug 11, 2003
Messages
11,696
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:33
Joined
Aug 30, 2003
Messages
36,118
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?
 

Zedster

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
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_Connection=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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:33
Joined
Aug 30, 2003
Messages
36,118
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/forums/topic/from-access-to-sql-server

which I pass the SQL and any other appropriate arguments to prior to using the query.
 

Zedster

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:33
Joined
Aug 30, 2003
Messages
36,118
Post back if you solve that. My impression is that most people do what I do, just change the SQL of a saved pass through query. I use the same one most of the time, for different purposes.
 

Zedster

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
Post back if you solve that. My impression is that most people do what I do, just change the SQL of a saved pass through query. I use the same one most of the time, for different purposes.

OK so I did some experimenting. I created a new blank database, with no tables, no queries just a vba code module. I then created a series of "SELECT" querydefs in VBA some that created stored querydefs, some that did not:

Code:
'stored/saved querydef
Set qdf = db.CreateQueryDef("qdfTemp")
'querydef that isn't stored
Set qdf = db.CreateQueryDef("")

All ran fine.

I then created a temporary table on the SQL server with a date field and ran a range of different SQL strings to insert dates in different formats using the same VBA methods as used in my first post.

The following SQL strings all entered the dates fine:

Code:
INSERT INTO  tblTestDates(DateTimeField) VALUES ('2019-12-19 07:31:12')
INSERT INTO  tblTestDates(DateTimeField) VALUES ('2019-12-19')
INSERT INTO  tblTestDates(DateTimeField) VALUES ('20191219 07:28:16')
INSERT INTO  tblTestDates(DateTimeField) VALUES ('20191219')
INSERT INTO  tblTestDates(DateTimeField) VALUES (CAST('20191219 08:23:15' as datetime))
INSERT INTO  tblTestDates(DateTimeField) VALUES (CAST(GETDATE() as datetime))

My conclusions:
  • It is possible to use VBA to insert data using a querydef directly to a SQL Server, with our without a stored procedure.
  • A completely table less front end database is possible
  • All of the above date formats insert fine into SQL through a VBA querydef
  • Previous finding are suggesting my main database is not functioning correctly. It appears that the querydefs are inserting to the local linked table rather than the table on the SQL server. This can be confirmed because if I delete the linked tables the subroutine falls over
I have a bit more work to do to find out what is wrong with my "real" database, but have a little more knowledge to help me.
 

Zedster

Registered User.
Local time
Today, 09:33
Joined
Jul 2, 2019
Messages
168
Post back if you solve that. My impression is that most people do what I do, just change the SQL of a saved pass through query. I use the same one most of the time, for different purposes.

Solved it. I discovered that the global constant for the connection string wasn't initialising. so qdf.connection was null. It appeared to therefore be running the insert query on the linked local table which happened to have the same name as the SQL Server table. Because the insert query was running on the local table and I was using date formats that were for SQL Server (no # #), the record was being added but the date not.

I have now managed to get my global constants to be in scope across the database and the original Insert query runs fine :)

Thanks for pointing me in the right direction.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:33
Joined
Aug 30, 2003
Messages
36,118
Thanks for the update. So if there's a connection string, it creates a pass through query, otherwise a regular query? That makes sense.
 

Users who are viewing this thread

Top Bottom