471,874 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,874 software developers and data experts.

MS Access to SQL server connection string

5 Nibble
Hi Folks. I have an access application that is using SQL server 2019 for the backend. All of my "queries" work but when the Update and insert statements execute Access will display the append or Update warning but nothing is actually being written or changed in the SQL tables. I believe I need to establish a SQLOLEDB connection string in my access project but I am having trouble. I get a message the states "Object Required". Any assistance would be greatly appreciated. Thank you. So far my code for the connection looks like this:
Expand|Select|Wrap|Line Numbers
  1. Dim ConnStr As String
  3. ConnStr = "Provider=SQLOLEDB;Data Source='FalconXtreme';" & _
  4.  "Initial Catalog='SacDb';Integrated Security= TRUE;"
  6.  objConn.Open ConnStr
Jun 14 '21 #1
4 3632
32,468 Expert Mod 16PB
So, from your description it's clear you're doing something wrong. What that might be is very hard to tell from the information you share. Your queries work without working :-S

Do you actually have a connection to the SQL Server in any way that allows updating of data? What are the queries working with? Don't leave us trying to guess what should be in your question.
Jun 14 '21 #2
404 Expert Mod 256MB
Following on from NeoPa's comments, please show an example of the code used in your update and insert SQL statements
Jun 14 '21 #3
5 Nibble
Can you share your execution of the sql statement?

For access front and sql be, you need to change your code for execution for delete, update and insert to "dbSeeChanges"


Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute sql, dbSeeChanges
Jun 29 '21 #4
32,468 Expert Mod 16PB
For access front and sql be, you need to change your code for execution for delete, update and insert to "dbSeeChanges"
That's very true. Worth a try if you haven't already.
Jun 29 '21 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by J. Muenchbourg | last post: by
5 posts views Thread by Fred Zuckerman | last post: by
1 post views Thread by madfisher | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.