Updating excel data with dsn less connection vba
I've been using ADODB for SQL queries to return data and copy it from a recordset to a workbook for a while and a new task is to update records but I have no clue on how to do update a record.
What is the correct approach for updating a record using VBA?
When I took out "DATABASE=" from the connection string it worked once, but not since then.
The SQL server is on a windows workgroup and 3rd party Active Directory is run by Novell, so I can't use windows authentication, I have to use SQL server authentication.
At least with Acces, unless you specifially want to give the user the ability to access the datasource outside of the app, I would not be fooling around with DSN's. DNS was the solution for me 1)Bunch of users (my own office people) have a bunch of VBA applications in excel and access. I prefer ADO, only because I'm familiar with it, and haven't quite gotten the hang of ADO. Cursor Location = ad Use Client and this is the error; "run-time error '424' object required" I have tried the code above ao730's post and on line "Set CN = New ADODB. Oracle.1; Password= Like everything else, database connections methods have evolved. NET is the "newest" method, and is suppose to be addressing the short comings of ADO, which in turn was addressing the short comings of RDO which was addressing the short comings of DAO. By the way, I still use a file on my machine to develop the connection string and feel it's a pretty useful and cheap tool. i've tried about 4 different bits of VBA to get a connection to work, the original VBA (created by someone who left before i joined) no longer works - think i'm missing the right reference in my VBA on my machine this is the code thats stopped working for me dbconnection. Oracle; User ID=highways; Password=highways; Data Source=swimsp" dbconnection. I've also looked for some sort of execute method but come up short. Open End With sql = "UPDATE crm_clients " & _ "SET cheque_number = '" & chq No & "' " & _ "WHERE id = '' " method but that only works for the record set itself not the database.
Why does the ODBC link update sometimes, but not other times?