ADO connection string and SQL 2

[VbPanicStation]

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!!

 

[RoyVidar]

1 - don't use a connection string, use currentproject.connection, which is a connection to the current database
2 - a search around here would surely provided some samples, here's one printing the result to the immediate pane (ctrl+g), where the recordset should also be updateable

[tt]dim rs as adodb.recordset
set rs = new adodb.recordset
with rs
set .activeconnection = currentproject.connection
.locktype = adlockoptimistic
.cursortype = adopenkeyset
.open "select * from sometable",,,,adcmdtext
debug.print .getstring
.close
end with
set rs = nothing[/tt]

typed not tested ...

Roy-Vidar

 

[Remou]

Here is a sample found by a search around:
Automation Process -Outlook and Access
thread705-724706