How to run an Update query for dates in sql server table that have linked Access tables

[jrdnoland]

I'm trying to run an update query from Access VBA to update a 2014 SQL Server table. The SQL server table(s) have linked Access 2016 tables.

If I use the Access syntax for dates (#) I get a type mismatch and the update doesn't work. If I use (') for dates I still get a type mismatch and the update doesn't work. If I run the update query (as it is below) in SQL query builder the query runs fine and performs the Update.

What's the solution to running a date update in SQL server with linked Access tables?

Update rdLab.tblSubTestRequests SET tblSubTestRequests.Project_Request =  1,tblSubTestRequests.IsRelease_Valid =  -1,tblSubTestRequests.ProjectName = 'Latest Project  Request',tblSubTestRequests.Objective = 'This is the  objective.',tblSubTestRequests.Project_Notification_Date =  '01/01/2000',tblSubTestRequests.Target_Completion_Date =  '01/01/2001',tblSubTestRequests.Requestor = 'Davidson,  Jeffrey',tblSubTestRequests.Requesting_Group =  'R&D',tblSubTestRequests.ECR_Number =  '12345',tblSubTestRequests.QBB_Test_Tracker =  -1,tblSubTestRequests.Results_Folder = 'Latest Project  Request',tblSubTestRequests.Project_Status =  'Reject',tblSubTestRequests.Project_Status_Notes =  'rejected',tblSubTestRequests.Processed_Release = -1 From  rdLab.tblSubTestRequests WHERE tblSubTestRequests.Counter = 1683 AND  tblSubTestRequests.Project_Release = 1;

[ranman256]

note that some sql tbl DATE fields are not really. Esp if you see a date like: 2/1/2021 13:05.122155
Even tho sql field property says Date , it is NOT. It has to be treated as a string.

I've seen date fields work updating with either # or ' ,but not neither. Except for that special date field above.
Do you have the most recent odbc driver for your SQL version?

[jrdnoland]

note that some sql tbl DATE fields are not really. Esp if you see a date like: 2/1/2021 13:05.122155
Even tho sql field property says Date , it is NOT. It has to be treated as a string.

I've seen date fields work updating with either # or ' ,but not neither. Except for that special date field above.
Do you have the most recent odbc driver for your SQL version?

When I use SQL Server Import and Export I'm using Microsoft Access (Microsoft ACE.OLEDB.16.0) for the data source and SQL Server Native Client 11.0 as the Destination.
I'm not sure when or if our IT is going to update the Destination driver.

Running some tests on my end, it seems using access to run an update query with a Date I need to use something like:

Public Sub Update_Table1()
Dim RS As DAO.Recordset
Dim SQL As String
Dim MyFirstDate As Date

MyFirstDate = #12/25/2021#
'Update Subtable also
    Set RS = CurrentDb.OpenRecordset("Table1", dbOpenDynaset, dbSeeChanges)
        SQL = ""
        SQL = SQL & "UPDATE Table1 SET "
        SQL = SQL & " [MyText]= " & "'This is me.',"
        SQL = SQL & " [MyInt]= " & 15 & ","
        SQL = SQL & " [MyDate]= #" & Format(MyFirstDate, "mm/dd/yyyy") & "#,"
        SQL = SQL & " [MyBit]= -1"       '//  True = -1, False = 0
        SQL = SQL & " WHERE (Table1.ID)= " & 2 & ";"
    Debug.Print SQL
    DoCmd.RunSQL SQL
End Sub

When running an update in ssms on a non-linked table I can use (') as the delimiter.
Not sure what to use when you are updating SQL server with an Access linked table.

Is the processing order SQL SERVER Table Field Update then ACCESS Table Field Update?

Thanks for helping with this, I'm on a deadline and am stuck with this issue

[jrdnoland]

I tried something else, but that is not working either. Perhaps someone could help with this attempt?

I tried this update code, it allows the code to be first processed in Access and then on SQL Server, or maybe that was the other way around???

Private Sub cmdOpenDatabase_Click()
Dim dbExample As DAO.Database
Set dbExample = DBEngine.OpenDatabase("K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\Jeff TEMP\JRD.accdb")

Dim SQL As String
Dim MyFirstDate As Date

MyFirstDate = #12/25/2021#
SQL = ""
SQL = SQL & "UPDATE JRD.Table1 SET "
SQL = SQL & " [MyText]= " & "'This is me again.',"
SQL = SQL & " [MyInt]= " & 15 & ","
SQL = SQL & " [MyDate]= #" & Format(MyFirstDate, "mm/dd/yyyy") & "#,"
SQL = SQL & " [MyBit]= -1"       '//  True = -1, False = 0
SQL = SQL & " WHERE (JRD.Table1.ID)= " & 2 & ";"
        
Debug.Print SQL
dbExample.Execute SQL
dbExample.Close
End Sub

When this is run it is still looking for a database somewhere else. How would I set it to the path referenced in the Set dbExample code?

[jrdnoland]

I tried something else, but that is not working either. Perhaps someone could help with this attempt?

I tried this update code, it allows the code to be first processed in Access and then on SQL Server, or maybe that was the other way around???

Private Sub cmdOpenDatabase_Click()
Dim dbExample As DAO.Database
Set dbExample = DBEngine.OpenDatabase("K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\Jeff TEMP\JRD.accdb")

Dim SQL As String
Dim MyFirstDate As Date

MyFirstDate = #12/25/2021#
SQL = ""
SQL = SQL & "UPDATE JRD.Table1 SET "
SQL = SQL & " [MyText]= " & "'This is me again.',"
SQL = SQL & " [MyInt]= " & 15 & ","
SQL = SQL & " [MyDate]= #" & Format(MyFirstDate, "mm/dd/yyyy") & "#,"
SQL = SQL & " [MyBit]= -1"       '//  True = -1, False = 0
SQL = SQL & " WHERE (JRD.Table1.ID)= " & 2 & ";"
        
Debug.Print SQL
dbExample.Execute SQL
dbExample.Close
End Sub

When this is run it is still looking for a database somewhere else. How would I set it to the path referenced in the Set dbExample code?

No one has had to run update queries with Dates in a linked Access Table to a SQL Server backend?

[ssanfu]

I'm trying to run an update query from Access VBA to update a 2014 SQL Server table. The SQL server table(s) have linked Access 2016 tables.

My main computer HDD decided to lay down on me and refused to boot up, so I'm still trying to recover from that.

I have a similar set up: A2010 FE with SQL Server Express 2014 BE. The FE is (was) connedted to the SQL Server via ODBC.
I could open a table and enter/edit the data directly.
I could use a query to enter/edit the data.
I could use a form to enter/edit the data.
I could use VBA to enter/edit the data.

What is confusing me is your statement "The SQL server table(s) have linked Access 2016 tables." I have never heard of this.... (but there is a lot of things I have never heard of).

From the Access FE, can you open a table and edit the data? And it is editing the SQL tables?

[jrdnoland]

My main computer HDD decided to lay down on me and refused to boot up, so I'm still trying to recover from that.

I have a similar set up: A2010 FE with SQL Server Express 2014 BE. The FE is (was) connedted to the SQL Server via ODBC.
I could open a table and enter/edit the data directly.
I could use a query to enter/edit the data.
I could use a form to enter/edit the data.
I could use VBA to enter/edit the data.

What is confusing me is your statement "The SQL server table(s) have linked Access 2016 tables." I have never heard of this.... (but there is a lot of things I have never heard of).

From the Access FE, can you open a table and edit the data? And it is editing the SQL tables?

Hi Steve, Access is the Front End and SQL Server is the Backend. This was set up by 1st importing the Access tables into sql server 2014. Then the Access tables were linked. (I'm really not sure if I could bypass the access tables and just have all the data in sql server.) Of course, the reason to continue to use access is because of all the functionality of the front end.

That's the way it is now, I can do anything with the sql server tables and data; but I can't manually change the access tables. I'm wondering if my import method is messing me up? If I change a table's structure in any way then I have to relink the Access tables.

I really don't have a clue at this point. This was supposed to be ready for "show and tell" on Monday. Not going to happen now, except by a miracle.

I do appreciate you reaching out and trying to help me. It's a bit frustrating to take the time to post and question and then either get no answers or one comment that really doesn't solve the problem.

[NoellaG]

Some possible solutions:
* create a pass-through query with the T-SQL syntax
* create in SQL server a procedure that does the update and call this procedure from Access VBA

examples : see https://stackoverflow.com/questions/...ored-procedure

[Minty]

PMFJI - This line

That's the way it is now, I can do anything with the sql server tables and data; but I can't manually change the access tables.

is a little confusing, just so we are getting the terminology correct, a SQL Linked table in Access is a SQL table.
You are able to open it directly in Access and in theory apart from design changes should be able to add/edit records exactly the same as an "inbuilt" local Access table.

If you can't, then the most likely cause is that the SQL table doesn't have a primary key defined, or also possibly that a Bit Field (Boolean Yes/No) isn't set up correctly. (See here https://nolongerset.com/yes-no-fields-in-sql-server/ for info!)

If that isn't the case, then are you saying you can't change the local Access tables?

[ssanfu]

Post #1

I'm trying to run an update query from Access VBA to update a 2014 SQL Server table. The SQL server table(s) have linked Access 2016 tables.

Post #7

Hi Steve, Access is the Front End and SQL Server is the Backend. This was set up by 1st importing the Access tables into sql server 2014. Then the Access tables were linked. (I'm really not sure if I could bypass the access tables and just have all the data in sql server.)

Let's start over.
Make a COPY of the Access FE. Rename the copy to something like "TEST_AccessFE2SQL".
Q1) All of the Access tables have been imported into SQL Server?
Q2) EVERY table in the SQL Server database has a Primary Key field?
Q3) SSMS can edit the data in every/all tables?
Q4) You have created an ODBC data source to the database in SQL Server? It is required to link to SQL Server tables.

Now open "TEST_AccessFE2SQL". DELETE ALL TABLES.
Go to the menu and click on "External Data". I don't have A2016 so I am not sure on the terminology.


See How to Link an Access Database to SQL Server in Access 2016

In the navigation pane, you should see the tables with an arrow and a globe before the name. The table name might begin with "dbo". You should rename the tables by deleting the "dbo" (I do so I don't have to do a lot of editing of queries\code).
If you open the table, you should be able to edit the data. (you have to use SSMS to change the table structure)