odbc connection to access

Hello,

I am a newcomer to databases in general and I am having some difficulties. I have an access database that i want to link to sql. I have trauled the web and notice that ODBC connection seems to be the recommended way.
I have set up a DSN which points to the access database but am now stuck as to what to do in sql to be able to query the database.
Could someone please help?
Many thanksHere is how you can connect. HTH

Set cnndb= New ADODB.Connection
cnndb.ConnectionString = "DSN=<dsnname>;UID=<userID>;PWD=<pwd>;"
cnndb.Open

setup record set .
set rs=new adodb.recordset
rs.open (<your sql statement>)

set rs=nothing
set cnndb=nothing|||Thank you sivaroo,
How do I query the connection?
I know it sounds thick but I really am i newboy|||To see if connection is successful you can use

if cnndb.state = 0 not connected
if cnndb.state = 1 connected

lets say you have sql like this.

strSql="Select * from table1"

once you created connection like previously mentioned.

dim rs as adodb.recordset
dim data1 as string

Set rs = Cnndb.Execute(strSql) 'this will pull data. then you can loop throug it to see
do while not rs.eof
data1=rs.fields("Column1").value
rs.movenext
loop

remeber to close like previously mentioned.|||Hello,

if you don't want to use VBA you can connect to your sql-server in different way.

Make up a new dsn for your sql server.
-> goto your tables object browser
-> press new
-> link table
-> filetyp : odbc database
-> tabstrip computer data source
-> choose your dsn
-> select your table

the table will now appear as a 'normal' table in your object browser.
You can query this like a regular table.
(Note that i use a german access so i only translated the menu items back to english)