How to create a connection in Access database?

[George]

Hi, I am working on an Access form, which is in an Access database.
On the form, I need open a table. But I have no idea how to set the
connection string.
The working environment is Windows XP Home Edition. Access is MS Office
2002.
I tried the following code:

Dim cnn As Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=c:/George/Database/students;UID=sa;PWD=pwd;"
cnn.Open

When open, the error message is:
"Data source name not found and no default driver specified."

what should I do in this circumstance?

Thanks!

George

 

[Gerald Stanley]

If the table is in the same database as the form you are
working on, then set cnn.ActiveConection =
CurrentProject.Connection

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Gerald,

This is how I do it. If you need to select data from the database.

dim rss as adodb.recordset
dim query as string

query = "select ....... from YOURTABLE Where ......." <--- I type the sql here
set rss = new adodb.recordset
set rss.activeconnection = currentproject.connection
rss.open query

if rss.eof then
msgbox "no records"
else
.......
end if
......

rss.close
set rss = nothing


If you are going to update the table, then I do:

query = "Update YOURTable Set ........ "
docmd.runsql query

I hope this helps

Luisa

 

[Gerald Stanley]

The line below is in error. It should have been
Set cnn = CurrentProject.Connection

Sorry for the confusion
Gerald Stanley MCSD