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:
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
'or, even easier way to do the above:
while not rsMyrecordset.EOF 'EOF=End Of File. Means until the end of the records
'do something here.
rsMyRecordset!MR_MYField = x
'add a new record:
'Fill in your field values as above
'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
Last edited by Greyson; August 9th, 2004 at 10:37 AM.
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
If you can see the table in the table view then no.
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
I'm off for the day but I or anyone else who reads this will be happy to answer any more questions on Jet