Hi,
    Please help!!! I have not used ado connection strings and SQL statements before so I am really stuck.
What I am trying to do is access some tables/ query within the same ACCESS 2003/ 2000 database using VBA and then use the data to send out an 'automatic' email.

Questions:
1. How do I set up a connection string in VBA such that I can access the data in the tables within the same ACCESS database?

2. How do I write and run the SQL statements to retrieve the data?

PLEASE HELP!!

Hi,
You don't need to create a connection for data in the same Access Database, you use the connection that Access has already created - you get at it through the  "CodeProject.Connection" Property.
So just declare your ADO Recordset, create the SQL String for the data that you want, and then open the Recordset.

Dim rst As ADODB.Recordset Dim strSQL As String Set rst = New ADODB.Recordset 'To only get a selection of data strSQL = "SELECT TableName.* FROM TableName WHERE ((TableName.IDField)=26)" rst.Open strSQL, CodeProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText 'To get a whole table rst.Open "TableName", CodeProject.Connection, adOpenStatic, adLockReadOnly, adCmdTableDirect

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thanks VB Lord!
May I please ask another silly question? How can you test to check what data you have retrieved inthe sql statement? Can you use MsgBox strSQL or print to a window?

Hi,
If you just want to make sure that you have got some data in the recordset before you try to do something with it then you can test to see where the cursor is sitting. If it is at both BOF and EOF then there are no records -

If Not (rst.BOF And rst.EOF) Then 'you have records rst.MoveFirst Do Until rst.EOF ..  do something .. .. rst.MoveNext Loop End If

If you want to see exactly what is in the Recordset then you could read the data into a String Variable as you go through the Loop (strData = strData & " - " & rst("Fieldname") ) and then display it in a MessageBox ( MsgBox strData) or you could put a Debug.Print rst("Fieldname") command in the Loop and then check the immediate pane to see what you got.
Does that help?




____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thank you so much Geoffs. You truely are a VB Lord!!

Code you gave me - works a treat - exactly what I wanted to do.

Thanks again

Vbpanic (now relaxed & chilled out!)