Help with Access DB and DataGrid

Hi

SInce this is my first work with Access DB and the Datagrid, i would need a little help. I have already built my Access DB File that contains 1 table called "Table1" and that table contains 4 fields.

in my VB code:

How do i write to my Access DB using an ADODB Recordset?
How do i read a specific cell on a specific row of my DB?
How do i clear all the data in my VB (just keep the Table and Fields name)?

thanks in advance

Martin

I don't ever use ado but use dao instead. i dunno which is better (have a feeling it's ado) but I can give you the dao 3.6 answers:

First, you need to specify a recordset. This can be called by an SQL query or by giving the whole table name (also SQL but not really a query).

Then, you can move around records and call field values. You can also delete, amend and apend using SQL or the vb recordset. here are the code egs:

Code:

---

Dim rsMyRecordset as recordset 'may need to be dao.recordset if your using both dao and ado.
Dim lngRecordcount as long
Dim x as long

set rsMyRecordset = currentdb.openrecordset("Table_Name")

rsMyRecordset.Movelast 'need to do this before recordcount for some reason
lngRecordcount = rsMyRecordset.recordcount
rsmyrecordset.movefirst 'you can move first before doing the recordcount if you need to
for x = 0 to lngRecordcount

    'Do something here if you like and use rs.movenext/rs.moveprevious/rs.move x

next 'x
'or, even easier way to do the above:
rsMyrecordset.MoveFirst
while not rsMyrecordset.EOF 'EOF=End Of File.  Means until the end of the records
    'do something here.
    rsMyrecordset.edit
    rsMyRecordset!MR_MYField = x
    rsMyRecordset.update
wend
'add a new record:
rsMyrecordset.addnew
'Fill in your field values as above
rsmyrecordset.update

'That should get you started with the VB method of editting a recordset.  Heres a quick look at the SQL method.

Dim strSQL as string

strSQL = "DELETE Table_Name.MR_MyField FROM TableName WHERE Table_Name.ID = 4;"

docmd.runsql(strsql) 'I find it easier to use a string var but you can just type the query in here instead.

'You can create your queries using the Access Query builder then copy the SQL into your code.

---

That should get you started anyway. Always look at the Help if I have used a term that you don't recognise or understand. If you get stuck then repost :)

NB1: For SELECT queries alway use a recordset coz a SELECT in the DoCmd.RunSQL will not do anything but tie up resources for the time to do the query.
Equally, you cannot use DELETE, INSERT etc in a recordset coz this is a command and not a search.

NB2: the table/field seperater is '.' or full stop/period where as the VB recordset/field seperater is '!' or exclamation point. (eg SQL = "SELECT MYTABLE.MYFIELD" and VB = MYRECORDSET!MYFIELD)

-edit- To select everything into a recordset use '.*' in the SQL and to delete everything use a DoCmd with "DELETE TABLE_NAME.* FROM TABLE_NAME;". That will delete everything. "KILL TABLE_NAME" deletes the table


Hope that helps :D
Greyson

Hello Greyson

Thanks ver much for the reply! :)

Got a question: Do i need to use a "Jet connection string" in my code?
That is something that is not clear to me.

Long answer:
if the tables are already in your access database or are already linked to the access database (in tables view with a black arrow in one corner) then there is already a JET connection in place. If you need to connect to, or link another datasource then you will need to connect to it with a connection string or JET connection

Short answer:
If you can see the table in the table view then no. :)

Exceptions:
If you have linked into a datasource that requires a password and didn't check the 'remember password' box when you linked it, then you will either need to use a connection string anyway or just delete the link and relink but remember the 'remember password' option. this option is a pit obscure but it is in there somewhere :D

I'm off for the day but I or anyone else who reads this will be happy to answer any more questions on Jet

I recently came by a basic article on ADO and Access that seems fairly well commented.


http://www.ftponline.com/archives/pr...007/gs0007.asp

Cathy

This is great stuff!


Thanks for sharing ;)


Martin