Referring to access records - Should be easy but not for me.

[pedie]

Hi, i have question on how to refer to range in access?
Suppose i want to edit ID no 107 instead of looping how can i go and directly refer to that record and edit/delete....

Thanks everyone

 

[Joe4]

"Range" really has no meaning in Access. That is an Excel term.
Data is stored in Access as "records". Their position is relation to other records in the table really has no meaning, unless you are sorting and looping through as record set.

What version of Access do you have?
If you have Access 2007 or later, just open the table/query where the data resides, click on the Filter button above the field you want to identify, and enter the value you want to look for. That will bring up the records meeting that criteria.

Or, to delete the record via code, you can use a SQL statement like:

DELETE [MyTableName].*
FROM [MyTableName]
WHERE [ID]=107;

You could also create a Delete Query to do that using the Query Builder instead of writing the SQL code directly.

 

[pedie]

Joe4, thanks for quick reply...
'm not familiar with sql line though it plays very important role in access data manipulation.

To be specific, i want to know how to refer to in this way like

[/FONT]
[FONT=Courier New]rst.edit[/FONT]
[FONT=Courier New]rst!myheadername(record/rownum) = "somthing"[/FONT]
[FONT=Courier New]rst.update[/FONT]
[FONT=Courier New]

Please advice me this way if there is anything like this...


Thanks again!

 

[Joe4]

Ah, you should have mentioned you are working with RecordSets. I typically do not use RecordSets for this sort of thing (basic Access queries do the job pretty well). But I found a link that discusses how you can do that: http://msdn.microsoft.com/en-us/library/bb243794(v=office.12).aspx

 

[pedie]

Ah, you should have mentioned you are working with RecordSets. I typically do not use RecordSets for this sort of thing (basic Access queries do the job pretty well). But I found a link that discusses how you can do that:

http://msdn.microsoft.com/en-us/library/bb243794(v=office.12).aspx

Thanks for the link Joe4

 

[Norie]

Pedie

Why can't you use 'normal' built-in Access queries etc with all the functionality the give you?

If you are coding everything then why are you even using Access?

It could probably be done with Excel as a frontend, or perhaps you could build your own application.

The latter would give you a lot more options, for example you wouldn't have an application to hide.

 

[pedie]

Pedie

Why can't you use 'normal' built-in Access queries etc with all the functionality the give you?

If you are coding everything then why are you even using Access?

It could probably be done with Excel as a frontend, or perhaps you could build your own application.

The latter would give you a lot more options, for example you wouldn't have an application to hide.

Norie, I understand, you're right....but it is that i want to know it in vba way to...Query...'m getting into it...
moreover, I dont know how to use the query/sql line within the code...
I have read so many things but cant understand it...

Pedie

 

[Norie]

Pedie

You've kind of got this the wrong way round.

If you do want to do this with code you will eventually have to write your own SQL statements.

So even if you know the code you also need to know SQL.

 

[pedie]

Pedie

You've kind of got this the wrong way round.

If you do want to do this with code you will eventually have to write your own SQL statements.

So even if you know the code you also need to know SQL.

Okay Norie
Thanks again!

 

[xenou]

Pedie

You've kind of got this the wrong way round.

If you do want to do this with code you will eventually have to write your own SQL statements.

So even if you know the code you also need to know SQL.

I agree with Norie - you must be strong with your SQL to be strong in any database development work.

It doesn't really matter but I see you are using ADO a lot. DAO is a natural fit with Access (its the "native language" of Access). You might find it easier to work with DAO if you're working with Access.

I use ADO for connecting to Access from Excel, or connecting to SQL Server, but I usually use DAO when I'm working with Access in Access.