Manipulate an Access 97 mdb database using Clarion via ODBC

Posted on 2005-04-20
Last Modified: 2008-02-01
Dear all,

Is it possible to manipulate (add, remove records and query) an Access 97 mdb from Clarion via ODBC, when I set up the connection i am getting a spurious error about invalid functions.

I tried connecting via ODBC from Access 97 to the database (normally one would use linked tables which does work) but the ODBC connection from Access 97 to teh database gives an error.

Can anyone advise on the correct setup?

Many thanks

Question by:kevinsmith121
    LVL 1

    Author Comment

    Here is a copy of the error

    Setting Connection timeout for Connection 1252228 Time Taken:0.00 secs
    Connecting to DSN=testing
    Warning: IM006[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

    Setting Asynchronous calling on for Statement 12523e8
    Error Occurred: S1C00[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented
     Time Taken:0.00 secs
    Known Backend Detected: Microsoft Access
    Maximum Statements per connection is 255
    LVL 4

    Accepted Solution

    This works for an ODBC connection to an Access97 database in visual basic.

    Set WS = DBEngine.Workspaces(0)
    Set db = WS.OpenDatabase(dbname, options, ro, connectionstring)

    dbname = ODBC data source name
    options =  true for excluseve, false for shared mode
    options for ODBC
    For ODBCDirect workspaces, the options argument determines if and when to prompt the user to establish the connection. You can use one of the following constants.
    dbDriverNoPrompt  =The ODBC Driver Manager uses the connection string provided in dbname and connect. If you don't provide sufficient information, a run-time error occurs.

    dbDriverPrompt =The ODBC Driver Manager displays the ODBC Data Sources dialog box, which displays any relevant information supplied in dbname or connect. The connection string is made up of the DSN that the user selects via the dialog boxes, or, if the user doesn't specify a DSN, the default DSN is used.

    dbDriverComplete =(Default) If the connect and dbname arguments include all the necessary information to complete a connection, the ODBC Driver Manager uses the string in connect. Otherwise it behaves as it does when you specify dbDriverPrompt.

    dbDriverCompleteRequired =This option behaves like dbDriverComplete except the ODBC driver disables the prompts for any information not required to complete the connection.

    ro = true for read only, false for read write

    Connectionstring = "ODBC; DATABASE=database; UID=user; PWD=password; DSN= datasourcename; [LOGINTIMEOUT=seconds;]"

    You could use the dbdriverprompt for trouble shooting or for added security to force the person to input a username and password. For Access 97 I believe the default  user is "Admin".

    Here are a couple of examples from the msdn vb6 help.

    Set dbsPubs = wrkJet.OpenDatabase("Publishers", _
                dbDriverNoPrompt, True, _

          ' Open read-only Database object by entering only the
          ' missing information in the ODBC Driver Manager dialog
          ' box.
          MsgBox "Opening second copy of pubs..."
          Set dbsPubs2 = wrkJet.OpenDatabase("Publishers", _

    dbDriverCompleteRequired, True, _

    Hope this helps.
    LVL 1

    Author Comment

    Many thanks for that, it helped me to sort out the problem.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now