updating records with forms 1

[kevpho]

Hi there,

I'm new to programming in visual basic, although I do know how to use ASP and VBScript. I've been trying to figure out how to create a simple script on a form that I can use to update certain fields on a table in the database. I want to be able to find all records with a certain property and change the fields appropriately, so it would not be practical to do it from a table as there are too many records.

So this is a fairly general question, but how do I begin to do this? From reading some tutorials I have created some forms in the database but haven't been able to connect to the tables even by using their code. I'm sure once I can get database access I can figure out how to do the updating, inserting, deleting ...

This is the code I'm using to connect to the database, and I have Microsoft DAO objects enabled (Access 2000, VB6)

Dim dbMyDB As Database
Dim rsMyRS As RecordSet

Set dbMyDB = OpenDatabase("MyDatabase.mdb")
Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)

I am getting a "Run-time error 3024", saying that the database is not found. Even when I place the full path in the string I get the same message.

Any help would be greatly appreciated. Thanks!

Kevin

 

[sillysod]

could try an update query.

and for more versitility a paramatized update query.

 

[willir]

Kevin

I suspect you are using Access 2000 or XP. You need to choose to use DAO or ADO. And you need to make sure you have added the appropriate reference.

Dim dbMyDB As DAO.Database, rsMyRS As DAO.RecordSet

Set dbMyDB = CurrentDB()
Set rsMyRS = dbMyDB.OpenRecordSet("MyTable")

Since you are using the table to update / insert, you can use the default...

Then continue...

With  rsMyRS 
    .addnew   '  for update, use .edit
        !Myfield1 = Me.FormField1
        !Myfield2 = Me.FormField2
    .update   '  have to update.
End With

rsMyRS.Close
dbMyDB/Close

As suggested by sillysod,

strSQL = "INSERT into ...

DoCmd.RunSQL strSQL

...Works too.

Richard

 

[kevpho]

Richard,

Thanks for your help, it was by not setting the variables as "DAO.Database" and "DAO.RecordSet" that I was unable to connect properly. I've made some good progress and think I can continue from here.

Kevin