MS Access to SQL server connection string

[rcsmith0712]

Hi Folks. I have an Access project with a sql server backend. The database was recently migrated from the original access backend to the now sql server backend. All of the queries work, but the insert and update statements are not modifying the tables/data even though access displays the append and update warning message when the insert and update statements fire. I think I need to add a connection string for the SQLOLEDB provider but I'm not sure how to do that. Also, in addition to the connection string, do I need to open a recordset for the table with data being added/modified? I added the following connection string code, Access returns a message that says "Object Required". Any assistance would be greatly appreciated. Thanks
Dim ConnStr As String


ConnStr = "Provider=SQLOLEDB;Data Source='FalconXtreme';" & _
"Initial Catalog='SacDb';Integrated Security= TRUE;"

objConn.Open ConnStr

 

[Isaac]

All of the queries work, but the insert and update statements are not modifying the tables/data even though access displays the append and update warning message when the insert and update statements fire.

I would focus on and scrutinize this statement, first.

In a normal scenario, Append and Update queries will work just fine on ODBC-linked tables. Especially if you are saying that the query appears to run perfectly with no error - and yet, doesn't update the table....Something is wrong with that. Either you're misinterpreting the "did Access give you an error or not" inside Access, or you're misinterpreting the SQL Server data "did the table get updated".

I can think of one exception, but this is really grasping at straws and very unlikely of course: A trigger on the SQL table that essentially reversed the changes. I once did this (Instead Of) to make a View non-updateable...although you could update it successfully from the client side, it wouldn't really stay updated. I was handing off a View to a MS Access user and wanted zero chances the view could be updated. There are numerous ways to accomplish this, but I was trying to learn Triggers at the time.

1. How are you running the update query in Access? What's the sql and what's the vba to execute it, if any?
2. How are you verifying that it took or didn't take?

 

[Ranman256]

i use:

dim conn As New ADODB.Connection

vDriver  = "sql server"
vSvr = "10.88.3.2"
vDB = "mySqlDB"

sConnStr = "Driver=" & vDriver & ";Server=" & vSvr & ";Database=" & vDb & ";Trusted_connection=Yes;ConnectionTimeOut = 5"

conn.ConnectionString = sConnStr
conn.Open

but I prefer ODBC DSNs.

 

[arnelgp]

check your SQL server Tables.
make sure that all tables have PK or Identity Column.
delete the Linked table and re-create them.

 

[Minty]

What @arenlgp said.
When you linked the tables make sure it identified the Primary Keys.
If it doesn't for any reason then any updates to the tables cannot be tracked because it can't "identify" the record(s) affected.

I would also be tempted to look at a newer driver, based on this documentation https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

That driver is deprecated for any new work, if you have moved to a new SQL server, it's really time to use the latest Driver which you can get from here https://docs.microsoft.com/en-us/sq...r-sql-server-on-windows?view=sql-server-ver15

 

[Isaac]

Although I agree that arnelgp's advice is good advice - and may be the root cause of why a table didn't get updated - and is worth bringing to the OP's attention. I was interested in stepping back even earlier in the original story, about the assertion that there is no error, as the first thing to be resolved. Because:

ODBC-linking a SQL Server table in Access that has no primary key (either marked as Primary Key in SQL Server--or chosen as such in the Access linking wizard popup) - will cause the linked table to be officially non-updateable.

As a non-updateable dataset, you would get an error when trying to update it: "Operation Must Use an Updateable Query" - whether you did this via CurrentDb.Execute/dbFailonError, or, an Update Query.

Which error OP says he is not getting....so I think there is something to be discovered & learned there, first. Because the 'first step' in the OP confusion is thinking that the Access query was working successfully.

The reason I think this is most important is imagine the consequences of misunderstanding that: It means you could go 6 months using a database, thinking a table got updated when it never did. Unrecognized errors are the worst IMO..