Tips: ODBC '& Access database

1. MS Access drivers (upto and including Access 97) are not thread safe.

2. Always make user your DoFieldExchange order is the same as the order of fields in the DB table. The class wizzard will occasionally change the order at you.

3. Don't use 16-bit Ints in your database. Only use Bytes or Longs (32-bit). The RFX_Short function actually uses SQL_LONG in the ODBC call. Most of the time this seems to work OK, but it will fail for paramaters. Also remember VC++ 5.0 ints are 32-bits.

4. Don't try and bind to fields you are passing in as paramaters. I.E. In the Access Query if you have a field you are compairing to a paramater, make sure the show row is not check for that column.

5. You can have Indexes with multiple fields besides the primary key to help speed up the DB, and help with Database integerity. To do this go into design view for the table, and select View->Indexes. at the bottom of the list enter a new index name, and then enter the names of the fields you wish to have in the index in the Field name colum in the next rows, leaveing index name cell blank

Index Name    Field Name   Sort Order
UserName      LastName     Accending
              FirstName    Accending
              MiddleInit   Accending
Index2        Field1       Accending
              Field2       Accending
Index3        Field1       Accedning
Index4        Field1       Accending

The index field properities will only appear when you are on the first row of the index. At this point you can make the index unique if you want. If you want the index to be unique, make sure all of the fields have at least 1 character. If one of fields is null, than that record will not be check against the Indexes rules. In the above example this means for anyone without a middle initail you need to make it a space. Otherwise you could end up with two John Smith's. Although it would check John T Smith correctly.

6. Always make sure that there is at least one field per table that is guantereed to be unique. If all else fails make an autoincrement field. You never know when you might need it.

7. To convert a CRecordset drived class to use a paramater query make the followning changes.

  1. In the c-tor add m_nParams = {number of paramaters}
  2. Change GetDefaultSQL to "{Call [ParamQuery] (?,?)}" you need a question mark for every paramater this example has two.
  3. In DoFieldExchange after the //}}AFX_FIELD_MAP add the following line
    pFX->SetFieldType(CFieldExchange::param);
    Then put the RFX calls for your paramaters.
    RFX_Long(pFX, _T("[Param]"), m_param);

8. Don't use CTime. To put it bluntly this class blows chuncks (IMHO). For ODBC use TIMESTAMP_STRUCT with the RFX_Date(), for DAO use COleDateTime, with DFX_DateTime(). The class wizard will always try and use CTime, you will have to change it by hand to the apporate type. CTime is limited to 1970 - 2038, and can crash your application if you try and give it an invalid date/time.

9. Be careful when using Bytes in your DB. Access uses the value of 255 (0xFF) as Null. So if the field is required it cannot equal 255.



Comments

  • "mailto"usage at runtime in VC++

    Posted by Legacy on 02/09/2004 12:00am

    Originally posted by: priyanka

    Im doing a project named “Activex employee builder” using VC++ with MS Access.
    In my project when the user types the employee name in textbox , the details of the employee popups in as floating menu.
    The Details r obtained at runtime from the database,.The details includes “Email
    Of the employee” . I would like to know how can I use “ mailto” utility of HTML into the Floating menu at runtime. Should I use DHTML?

    Reply
  • How can I accelerate work of DB using ADO and Access?

    Posted by Legacy on 09/21/2003 12:00am

    Originally posted by: Seytek

    How can I accelerate work of DB using ADO and Access?
    My Database is big one_table = 260 Mb! So its working so slowly. Help, please.

    Reply
  • Getting system tables information

    Posted by Legacy on 08/22/2003 12:00am

    Originally posted by: Pawan Deshpande

    How can I get the information about the tables. I know there is a system table named MSysObjects. But this table just gives the names of the tables in the database. I want information about the fields in the tables. Like field name, field length, nullable or not and so on.

    I would greatly appreciate if you tell me, in which system tables this information is stored. As in case of Oracle the metadata about all columns in tables is stored in a system table named as USER_TAB_COLUMNS. We can get information about the field of a table from this table. There must be a similar way in access too.

    Thanks

    Pawan Deshpande.

    Reply
  • how to get creation date of Access database with ODBC

    Posted by Legacy on 06/25/2003 12:00am

    Originally posted by: mattes

    I need the creation date of an Access database via OBDC. Is it poosible?

    Reply
  • Developing simple search engine

    Posted by Legacy on 03/03/2003 12:00am

    Originally posted by: Majid Matlub

    Hi,

    I have never been a formal computer student so my knowledge about programming or databases is just like that of a layman. I have just designed my website by using wizards of MS Frontpage. I have a search page on that website. I want that search engine to search from some of my folders and display the results in a list like the most search engines do.

    Please tell me how can I do it. I don't have a large database. It is just short list of the .mpg files that I have on my computer. Please consider while replying that I do not understand the languages or codes like what you say VB or OBDS etc.

    Thank you for sparing sometime for me.

    Majid
    Islamabad
    Cell: (0092) 300 519 5070

    Reply
  • i need help

    Posted by Legacy on 12/15/2002 12:00am

    Originally posted by: muchai nganga

    how good is microsoft access compared to other database management systems??
    

    Reply
  • Immediately reading a new record

    Posted by Legacy on 12/13/2002 12:00am

    Originally posted by: Dennis

    I was hoping to find a solution the the following problem:

    I was to add a new record using CRecordset::AddNew.
    My Primary key a an autonumber field.
    After I added the record, I want to know which primary key value it was given.

    Please help me with this.

    Reply
  • Compacting a ODBC Database?

    Posted by Legacy on 08/07/2002 12:00am

    Originally posted by: Andy

    Hi
    
    I'm using a ODBC Access database (I steered clear of DAO, since I was advised it was not part of UDA and therefore not as future-proof) and cannot seem to get rid of the deleted record fields. I have a file that is 600K, but when viewed in Access only contains one record. This is due to the number of additions and deletions this database has had performed on it.
    I know that DAO has a nice CompactDatabase method, but can I do the same thing for my ODBC database?

    Many thanks

    Andy

    Reply
  • Help!!! Error in sql query statement .(urgent :( )

    Posted by Legacy on 04/04/2002 12:00am

    Originally posted by: Mustafa

    Friends,
    I am using CRecordset::open
    with the following query:
    "SELECT Field1,Field2
    FROM Mytable
    WHERE (([Mytable]![Field2] In (SELECT Field2
    FROM Mytable
    WHERE (([Mytable]![Field1]<>[Mytable]![Field2])))))
    ORDER BY Field2"

    i am getting an exception as follows:

    Too few parameters. Expected 2.
    State:07001,Native:-3010,Origin:[Microsoft][ODBC Microsoft Access Driver]

    First-chance exception in PRP.exe (KERNEL32.DLL): 0xE06D7363: Microsoft C++ Exception.

    I am new to database programming.

    Please help me.
    any help would be greatly appreciated.

    Reply
  • TIMESTAMP_STRUCT

    Posted by Legacy on 05/29/2001 12:00am

    Originally posted by: bidner martin

    thnx a lot 4 this article, it help to solve the DDX drouble, i had with CTime in an ODBC - DB
    

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • In support of their business continuity and disaster recovery plans, many midsized companies endeavor to avoid putting all their eggs in one basket. Understanding the critical role of last-mile connectivity and always available Internet access for their enterprises, savvy firms utilize redundant connections from multiple service providers. Despite the good intentions, their Internet connectivity risk may still be in a single basket. That is because internet service providers (ISPs) and competitive local …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds