Recordset update

Posted on 1998-07-27
Last Modified: 2010-05-18
I have form level recordset variable. And I am using an SQL statement in a routine and using Set Testset = test_Db.OpenRecordset(Sql, dbOpenDynaset, 0, 1). But the problem is when I tried to update my 'Testset' recordset it is not updating. I am using
           Bldset!BLDGCODE = txtBuilding.Text
           Bldset!BLDGNAME = txtName.Text

It is not updating and is not showing any errory message also. And I am sure I am opening my database as read/write mode
Question by:svenkatapuram
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1089258
If the code you've given is correct, you need to replace all occurances of "Bldset" with "Testset", as in


Also, you could use a With...End With to avoid typing Testset over and over. Just use the period "." and exclamation point "!" inside the With...End With pair.

Author Comment

ID: 1089259

It was a typo in my question, it was Bldset. Some how my recordset is setting to read only. I do not know why? Any solution

Expert Comment

ID: 1089260
Are you sure there isnt any error message??? If there isnt any error message, then the process should be ok! Was you app connecting to a .mdb before???
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Author Comment

ID: 1089261
Originally my app was using a .mdb file and later I changed to SQL server. For some tables it is updating (when I use recordset.edit) but for some it is not. But when I use direct SQL commands like update it is working. So any way I started changing my code
to SQL commands instead of recordset.


Accepted Solution

DennisH earned 50 total points
ID: 1089262
One of the most common reasons that you can't insert or update a record in a SQL Server table is if the table does not have a Primary Key.  Does this table have a Primary Key?

Author Comment

ID: 1089263
I do have primary key for my tables

Expert Comment

ID: 1089264
Then I know what is your problem!!!

Expert Comment

ID: 1089265
please check some of your paths are incorrect! When you use SQL statement, pls check to see if you path to the SQL Server is correct. If you dont even have a path to the SQL Server for edit or update, or I should say when you want to send SQL statement for execution in the SQL Server, you should have a proper connection established before you execute any SQL. I think you still have some of the connections pointed to the .mdb instead of SQL Server since you have used the .mdb before. Double check the .mdb to see if updating has been done!!! I used to overlook things like those, too!

Author Comment

ID: 1089266
I do have porper connection to the SQL server. I thing I figured out the problem. Because I am setting the recordset(testset) in one routine, then I am passing the same SQL statement to another routine where I am using the following to populate the grid
FillGrid(f As Form, Ssql As String)
Data1.Connect = "odbc;uid=sa;pwd=;dsn=NAME"
Data1.RecordSource = Ssql$

Then I am resetting the data1.recordset to the first recordset(testset). Becuase of this it is not working. I do not have to reset again. I can use the original recordset itself for the Grid also. Any way thank you for your suggestion

Author Comment

ID: 1089267
I already added comment to your reply


Expert Comment

ID: 8427360

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Linked Server - SP with Param to VIew 7 25
access to sql migration 5 26
8 hour Continual coverage based on Time in and Time Out 9 21
SQL profiler 3 21
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question