August 9th, 2004, 10:27 AM
Help with Access DB and DataGrid
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
August 9th, 2004, 11:34 AM
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:
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
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.
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 11:37 AM.
August 9th, 2004, 11:49 AM
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.
August 9th, 2004, 12:00 PM
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
August 9th, 2004, 12:06 PM
I recently came by a basic article on ADO and Access that seems fairly well commented.
Jan 2004 - NEWBIE to VB6
Any and all help appreciated
August 9th, 2004, 01:34 PM
This is great stuff!
Thanks for sharing
Click Here to Expand Forum to Full Width