?
Solved

Manipulate an Access 97 mdb database using Clarion via ODBC

Posted on 2005-04-20
3
Medium Priority
?
653 Views
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

Kevinsmith121
0
Comment
Question by:kevinsmith121
  • 2
3 Comments
 
LVL 1

Author Comment

by:kevinsmith121
ID: 13822354
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
0
 
LVL 4

Accepted Solution

by:
RonHoffmann earned 2000 total points
ID: 13824384
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, _
            "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

      ' 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, _
            "ODBC;DATABASE=pubs;DSN=Publishers;")

      
Hope this helps.
0
 
LVL 1

Author Comment

by:kevinsmith121
ID: 13834695
Many thanks for that, it helped me to sort out the problem.

Thanks

Kevin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

599 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