Fog Creek Software
Discussion Board




This should be easy part II ...


I've got an access database.

I want to access it via ADO or ODBC in VC++.

I'd prefer to use a CDatabase Object.  Not a DAO object.

I just need some code like this:

Cdatabase db;
db.open(??, FALSE, FALSE, ??, TRUE);

to open the connection.

Like an ado connection string.

I've tried various derivaties of:

CDatabase db;
BOOL bResult = db.Open(_T(""), FALSE,
            FALSE,
            _T("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\Test.mdb;"),
            TRUE);

they don't seem to work.

I even tried importing ADO in my stdafx.h and a line like this:

_ConnectionPtr Database;
Database.CreateInstance(__uuidof(Connection));
HRESULT hr;
const _bstr_t DSN = "Provider=Microsoft.Jet.OLEDB.3.0; Data Source=D:\\Test.mdb;";
    if (FAILED(hr = SIFDatabase->Open(DSN, "", "", NULL)))

It doesn't work either.

Little help?

thanks,

Matt H.
Friday, January 10, 2003


The "SifDatabase" above should read "database"

It does compile, that's not the problem.  :-)

Matt H.
Friday, January 10, 2003

Load your Visual Basic and create a new project
Select Data View in the View menu.
Right click on "Data Links"
Select "Add a Data Link"
- follow instructions, select your database
Right click on DataLink1
Select Properties and read the correct string to open the database.

pub
Friday, January 10, 2003

Oh, this is very microsoft. Load VB just to find out a string to load a db! How is everything screwed up!

Passater
Friday, January 10, 2003

Passater,

I'm sure there is simpler solution.

pub
Friday, January 10, 2003

If you need the ADO connection string for Access (JET), try this site:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet
or start here:
http://www.able-consulting.com/ado_conn.htm

Also note that the JET drivers are no longer installed with the latest versions of ADO/MDAC and must be downloaded from Microsoft's site and installed separately - see:
http://www.microsoft.com/data/download.htm#Jet4SP3info
If you're asking specifically about the CDatabase call syntax, sorry I'm not familiar with it.

Philip Dickerson
Friday, January 10, 2003



I ended up using ADO smart-pointers, but I'm still -very- interested in how to use the CDatabase class.  If I can figure it out, i'd probably even re-factor my code.

Any pointers, articles, links, ideas, etc?

This has got to be a common problem, but all my ADO books say things like:

.open(DSN, some_bool, connectionstring)

they don't actually tell you how to build a dsn.  (I know, everybody uses system DSN's, but you can't do that when you're shipping speculative software ...)


regards,

Matt H.
Friday, January 10, 2003

why can't you build the string with a UDL file?  just create a textfile, rename the extension to .UDL, then double click on it.  it pops up a nice little dialog that allows you to build the connection string and test the connection.  when you're done, open the icon in notepad or something, and copy & paste the connection string.

nathan
Friday, January 10, 2003

www.connectionstrings.com -- couldn't be in business without it!

Joel Spolsky
Friday, January 10, 2003

I just did a quick read about the "Open" method in the CDatabase class, starting here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/_MFC_CDatabase.asp
and it appears that you have to use an actual ODBC DSN, which has been constructed on the system on which the code is running. You cannot use DSN-less connection strings that are typically used with ADO. However, it does state that "you can pass NULL if you want to present the user with a Data Source dialog box in which the user can select a data source", so that's one option. [You may also want to look at the "OpenEx" method instead of the "Open" method, although it still requires an ODBC DSN (or a user dialog).]

Philip Dickerson
Saturday, January 11, 2003

The CDatabase::Open method is:
virtual BOOL Open(
  LPCTSTR lpszDSN,
  BOOL bExclusive = FALSE,
  BOOL bReadOnly = FALSE,
  LPCTSTR lpszConnect = _T("ODBC;"),
  BOOL bUseCursorLib = TRUE
);

lpszDSN:
Specifies a data source name — a name registered with ODBC through the ODBC Administrator program. If a DSN value is specified in lpszConnect (in the form "DSN=<data-source>"), it must not be specified again in lpszDSN. In this case, lpszDSN should be NULL. Otherwise, you can pass NULL if you want to present the user with a Data Source dialog box in which the user can select a data source.

lpszConnect :
Specifies a connection string. The connection string concatenates information, possibly including a data source name, a user ID valid on the data source, a user authentication string (password, if the data source requires one), and other information. The whole connection string must be prefixed by the string "ODBC;" (uppercase or lowercase).

Therefore, there are 2 choices for specifying the DSN and additional connection information (leaving out some of the details like the _T():
(1)
Open("MyDSN-Name",FALSE,FALSE,"ODBC;UID=MyDBUserName;PWD=MyDBpwd",TRUE)
(2)
Open(NULL,FALSE,FALSE,"ODBC;DSN=MyDSN-Name;UID=MyDBUserName;PWD=MyDBpwd",TRUE)

In either case, the DSN "MyDSN-Name" must already exist on the system (created through the ODBC Administrator applet).

Philip Dickerson
Saturday, January 11, 2003


So the DSN HAS TO BE a system DSN, setup by the user or automated?  (ODBC settings) - instead of a file DSN like:

C:\Matt.mdb Proviver=Microsoft Access Data (*.mdb)

?

That's LAME.


regards,

Matt H.
Monday, January 13, 2003

Use OpenEx to open a DSN-less connection string.

Keith Wright
Monday, January 13, 2003

It is. I use this as a sample DSN:

CString m_strDsn = _T("ODBC;DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};DSN='';DBQ=C:\\projects\\lemeridien\\Database\\smsv2.mdb;UID='';PWD='';");

Notice the DSN=''

If you don't have that it gives you weird results. Must put some time aside on the infinite weekend to study dbcore.cpp.

Here's the code:

CDatabase *db = new CDatabase();
CRSTaskData* rs = NULL;
try {
  _tprintf( _T("Opening %s\n"), m_strDsn );
  db->Open( NULL, FALSE, FALSE, m_strDsn, TRUE );
  _tprintf( _T("Adding record\n") );
  rs = new CRSTaskData( db );
  rs->Open(  ); // You could use the select...1=0 here.
  rs->AddNew();
  // The following is just a bunch of fields to illustrate
  rs->m_c_year  = 2004;
  rs->m_c_month  = 6;
  rs->m_c_day    = 1;
  rs->m_c_card  = 1;
  rs->m_c_task  = 1;
  rs->m_c_assignee  = _T("kim");
  rs->m_c_done  = TRUE;
  CTime d( 1970, 1, 1);
  rs->m_c_actioned  = d;
  rs->m_c_completed  = d;
  rs->m_c_memo  = _T("Memo1");
  // And now do it.
  rs->Update();
  rs->Close();
  _tprintf( _T("Closing %s\n"), m_strDsn );
  db->Close();
} catch (CDBException *e) {
  _tprintf( _T("%s\n"), e->m_strError );
  e->Delete();
}
if (rs) delete rs; // Zap it if it's real

Don't forget to initialize the CTime's in the recordset constructor:

CRSTaskData::CRSTaskData(CDatabase* pdb)
    : CRecordset(pdb)
{
    CTime d(1970, 1, 1);
    //{{AFX_FIELD_INIT(CRSTaskData)
    m_c_year = 0;
    m_c_month = 0;
    m_c_day = 0;
    m_c_card = 0;
    m_c_task = 0;
    m_c_assignee = _T("");
    m_c_done = FALSE;
    m_c_memo = _T("");
    m_c_actioned = d; // Here
    m_c_completed = d; // And here
    m_nFields = 10;
    //}}AFX_FIELD_INIT
    m_nDefaultType = snapshot;
}

My 2c. Oh and don't flame me for CTime. It's just an example.

Kimbo.
Senior Software Engineer
DCG Media Ltd

Ms Kimberley Scott
Monday, August 23, 2004

Whoops. DSN got truncated. Better example:

CString m_strDsn =
  _T("ODBC;")
  _T("DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};")
  _T("DSN='';")
  _T("DBQ=C:\\Someplace\\smsv2.mdb;")
  _T("UID='';")
  _T("PWD='';")
;

The DSN=''. UID='' and PWD='' are single ticks, not rabbit ears. :-0

Ms Kimberley Scott
Senior Software Engineer
DCG Media Ltd

Ms Kimberley Scott
Monday, August 23, 2004

*  Recent Topics

*  Fog Creek Home