Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC to locate a set of *.dbf (Word/Xp et al.)

    I'm going bonkers trying to get a list of dBaseIII tables from an ODBC connection. The application is supposed to enable a user to browse to a file/folder/http and locate a database. Once I have a database, the theory goes, I can offer a list of tables, and from any one table, I can offer a list of fields.

    I'm quite happily learning about ODBC from Control panael, and would like to standardise my approach on that. My approach seems to break down when I switch from a single-file-as-database (e.g. Access/MDB, LotusNotes/NSF) to a multiple-file-in-folder-as-database (as in Act!6/*.dbf).

    The first two examples work fine. The application finds the database file, explores the tables, fields etc.

    The third example doesn't do what I want it to do - I can't make it find the *.dbf in the folder, as a set of table names.

    I could, of course, revert to DIR/FSO etc. to list the *.dbf from the folder, but I feel that ODBC drivers should be capable of doing this for me.

    Have I overlooked a basic design feature somewhere? Should I continue to go bonkers, or is ODBC absolutely NOT the right tool for a collection of DBF in a single folder?


    Here I am with LotusNotes<pre> 'connect to server using Lotus NotesSQL Driver
    Set Aconn = New ADODB.Connection
    Aconn.ConnectionString = "Driver={Lotus NotesSQL Driver (*.nsf)};Database=" & strDatabase & ";"
    Aconn.Open
    '''
    ''' Open the database schema to query the list of tables
    '''
    Set rst = Aconn.OpenSchema(adSchemaTables)
    '''
    ''' Loop through the list and process each table name
    '''
    frmTables.lbTables.Clear
    Do While Not rst.EOF
    frmTables.lbTables.AddItem (rst("TABLE_NAME"))
    rst.MoveNext
    Loop</pre>



    Here I am with MSAccess<pre> 'connect to server using Microsoft Access Driver
    Set Aconn = New ADODB.Connection
    Aconn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & strDatabase & ";"
    '''
    ''' Open the database schema to query the list of tables
    '''
    Set rst = Aconn.OpenSchema(adSchemaTables)
    '''
    ''' Loop through the list and process each table name
    '''
    frmTables.lbTables.Clear
    Do While Not rst.EOF
    frmTables.lbTables.AddItem (rst("TABLE_NAME"))
    rst.MoveNext
    Loop</pre>


    Here I am with Act6(*.dbf folder). The connection appears to work, but the rst is empty - .Count = zero<pre> 'connect to server using Microsoft dBASE Driver
    Set Aconn = New ADODB.Connection
    Aconn.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};Dbq=" & strDatabase & ";"
    Aconn.Open
    '''
    ''' Open the database schema to query the list of tables
    '''
    Set rst = Aconn.OpenSchema(adSchemaTables)
    '''
    ''' Loop through the list and process each table name
    '''
    frmTables.lbTables.Clear
    Do While Not rst.EOF
    frmTables.lbTables.AddItem (rst("TABLE_NAME"))
    rst.MoveNext
    Loop</pre>

    (later) <!post=This post,234571>This post<!/post> introduced me to the concept of a catalogue. The code below works for a set of *.CSV in a single folder; however I still can't migrate this code to a set of *.dbf in a single folder.<pre> 'connect to server using Microsoft Text Driver
    Set Aconn = New ADODB.Connection
    Set Aconn = New ADODB.Connection
    Aconn.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & "Initial Catalog=" & strDatabase & ";"
    Aconn.Open
    '''
    ''' Open the database schema to query the list of tables
    '''
    Set rst = Aconn.OpenSchema(adSchemaTables)
    '''
    ''' Loop through the list and process each table name
    '''
    frmTables.lbTables.Clear
    Do While Not rst.EOF
    ' Call TableChecker(Aconn, rst("TABLE_NAME"), docResult)
    frmTables.lbTables.AddItem (rst("TABLE_NAME"))
    rst.MoveNext
    Loop</pre>


    (later still) Got it! No I haven't; this code is suspect]<pre> 'connect to server using Microsoft dBASE Driver
    Set Aconn = New ADODB.Connection
    Aconn.ConnectionString = "Driver={Microsoft dBase Driver (*.dbf)};DBQ=" & strDatabase & ";"
    Aconn.Open
    '''
    ''' Open the database schema to query the list of tables
    '''
    Set rst = Aconn.OpenSchema(adSchemaTables)
    '''
    ''' Loop through the list and process each table name
    '''
    frmTables.lbTables.Clear
    Do While Not rst.EOF
    frmTables.lbTables.AddItem (rst("TABLE_NAME"))
    rst.MoveNext
    Loop</pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ODBC to locate a set of *.dbf (Word/Xp et al.)

    Does this work? It requires a reference to both ADO and ADOX.

    Dim strDatabase As String
    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table

    strDatabase = "..."
    cnn.Open ConnectionString:="Driver={Microsoft dBase Driver (*.dbf)};DBQ=" & strDatabase & ";"
    Set cat.ActiveConnection = cnn

    frmTables.lbTables.Clear

    For Each tbl In cat.Tables
    frmTables.lbTables.AddItem tbl.Name
    Next tbl

    Set tbl = Nothing
    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ODBC to locate a set of *.dbf (Word/Xp et al.)

    Omit the trailing backslash from the path:

    strDatabase = "C:GreavesClientsBBBIGTMAACTDatabase"

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC to locate a set of *.dbf (Word/Xp et al.)

    NO. Thanks, but no.
    <pre> 'strDatabase = "C:GreavesClientsBBBIGTMAACTDatabase"
    'strDatabase = "C:GreavesClientsBBBIGTMAACTDatabase"
    strDatabase = "C:GreavesClientsBBBIGTMAACTDatabaseGTMA development-TEST.dbf"</pre>

    The first two give me no tables (I don't pass through the loop), but the second gives me an error. The error is pretty well what i expect, because I think of a *.dbf database as a collection of single dbf tables in a folder. In this case i expcted to get a list of all *.dbf in the nominated folder.

    However, I'm intrigued, and will explore further. I think my problem here might be that the *.dbf are not regular dbaseIII, but are a form of dbase4, to wit Act!6 database tables.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC to locate a set of *.dbf (Word/Xp et al.)

    > Omit the trailing backslash from the path

    Right. Thanks. My mistake during the paste-to-post.

    The code I have works well for what I think of as standard old-fashioned dbase3 files; I have tested it on my DOS-based PCFileDb collection. The code falls on its face when I try to access a dbf that is an Act6 Contacts database file. For that reason I suspect that my problem lies in the driver, that is "{Microsoft dBase Driver (*.dbf)};FIL=dBase4" just won't cut it for an Act6 *.dbf, and I'll need to locate a dbase4 driver, or a driver specific to Act6. When I've got this working I'll post it as a module here. I have to implement Outlook and other stuff such as Blackberry, or whatever fruit is in season. (There's a rumour that December's snow might melt real soon now ..... who'd have thawed it?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ODBC to locate a set of *.dbf (Word/Xp et al.)

    The standard Microsoft dBase Driver works for dBase III, dBase IV and dBase 5; I tested the code on files in each of these formats. If Act6 uses a modified format, you would indeed need a special driver.

Posting Permissions

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