var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: odbc connection to access
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?
Here is how you can connect. HTH
Set cnndb= New ADODB.Connection
cnndb.ConnectionString = "DSN=<dsnname>;UID=<userID>;PWD=<pwd>;"
setup record set .
set rs=new adodb.recordset
rs.open (<your sql statement>)
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
remeber to close like previously mentioned.
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)