Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Post Unanswered: 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 thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    76
    Here 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

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    Thank you sivaroo,
    How do I query the connection?
    I know it sounds thick but I really am i newboy

  4. #4
    Join Date
    Nov 2003
    Posts
    76
    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.

  5. #5
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    8
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •