Strategy for getting data from Access and Excel databases

I need to get data from Access and Excel databases for interpretation in a VB application. The VB application is set up to work with the data in an array, and I can fill the array with data from an Access database or Excel spreadsheet providing I know EXACTLY what the field headings are going to be. For example, I can populate the array with data from a database or spreadsheet with a field called UnitID. However, if I try populating the array from a database or spreadsheet where the same parameter is called Unit#, I have to (a) be aware of the different field names in the databases and (b) provide code to acccomodate these differences. Does anyone know of a way where the user can first identify the fields in a given database, then select or rename them for download to the  VB application. I need this because users will want to use their own databases with my application, and I don't want to customise the application for each and every user.

Is there a strategy for doing this?

Any help will be appreciated..

100 points to start with....

Thanks

David
DrDAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
amebaConnect With a Mentor Commented:
This loop:
>   For i = 1 To gNumFields
>       gFields(i) = GetSetting(App.Title, "DB", "Field" & i, "")
>   Next
can be replaced with this:

    gFields(1) = GetSetting(App.Title, "DB", "Field1", "")
    gFields(2) = GetSetting(App.Title, "DB", "Field2", "")
    gFields(3) = GetSetting(App.Title, "DB", "Field3", "")
    gFields(4) = GetSetting(App.Title, "DB", "Field4", "")


Function GetSetting(AppName As String, Section As String, Key As String, [Default]) As String
    Returns a key setting value from an application's entry in the Windows registry

1. First parameter (App.Title) is used in Registry to identify your app.  Registry is central repository and each application has its own 'branch'

2. You application can have more sections:
I put all in section "DB"

3. is a Key

4. parameter - you can give default values, e.g. if you distribute your database "data.mdb", you can set default values:

> gDBName = GetSetting(App.Title, "DB", "DBName", "")
gDBName = GetSetting(App.Title, "DB", "DBName", App.Path & "\data.mdb")

Those 'initial' values will be used if registry entry does not exist, i.e. when your app is run for the first time.

In old INI file format, entries would look like this:

[DB]
DBName=c:\project\data.mdb
Table=Publishers
Field1=ID
Field2=UnitName
Field3=Price
Field4=Description

but that format has limitations (e.g. max 64K of data), must be distributed with each application, when app is reinstalled, no data is preserved, not good for binary data, ...
0
 
amebaCommented:
Here is the UI: two comboboxes, one button and multiline textbox:

Combo1      Combo2     Command1

-------------------------------
Unit ID = tb.ID


-------------------------------

Fill Combo1 from array myFNames.
Fill Combo2 with all field names from table in database (user will select database and table)

User will have to select item from left combo and map it to item from second combo

' form1 code -----------------------------------------------
Option Explicit
Dim myFNames() As String   ' "Unit ID", "Unit Name"
Dim userFNames() As String ' to be filled by user

Private Sub Command1_Click()
    Dim idx As Integer, i As Long, sql As String
   
    If Combo1.ListIndex > -1 And Combo2.ListIndex > -1 Then
        idx = Combo1.ItemData(Combo1.ListIndex)
       
        userFNames(idx) = Combo2.List(Combo2.ListIndex)
        ' remove from combos
        Combo1.RemoveItem (Combo1.ListIndex)
        Combo2.RemoveItem (Combo2.ListIndex)
        ' show info in textbox
        Text1.Text = Text1.Text & myFNames(idx) & " = " & userFNames(idx) & vbCrLf
       
        If Combo1.ListCount > 0 Then
            Combo1.ListIndex = 0
            Combo2.ListIndex = 0
        Else
            ' buid sql statement
            sql = "Select "
            For i = 0 To UBound(myFNames)
                If i > 0 Then sql = sql & ", "
                sql = sql & "[" & userFNames(i) & "]"
            Next
            sql = sql & " from " & "tablename"
           
            MsgBox "Mapping done.  To retieve data use sql statement:" & vbCrLf & sql
        End If
    End If
End Sub

Private Sub Form_Load()
    Dim i As Long
   
    myFNames = Split("Unit ID,Unit Name,Unit Price", ",")
    ReDim userFNames(0 To UBound(myFNames))
   
    ' fill left combo
    For i = 0 To UBound(myFNames)
        Combo1.AddItem myFNames(i)
        Combo1.ItemData(Combo1.NewIndex) = i
    Next
   
    ' fill right combo from table in user database
    Combo2.Clear
    Combo2.AddItem "ID"
    Combo2.AddItem "someName"
    Combo2.AddItem "somePrice"
   
    Combo1.ListIndex = 0
    Combo2.ListIndex = 0
End Sub
' -----------------------------------------------------

You should also add code to select database file (using CommonDialog),
code to fill another combo "cbTables" with tablenames in database,
and in cbTables_Click code to fill Combo with FieldNames from selected table (use Fields collection if you use DAO)
0
 
amebaCommented:
Two snippets if you are using DAO:

'code to extract tablenames
    Dim atables() As string  ' array of tables
    ReDim atables(1 To 255)
    numTables = 0
    For Each tdf In mdbCurrentDB.TableDefs
        If (tdf.Attributes And &H80000002) = 0 Then
            numTables = numTables + 1
            atables(numTables) = tdf.Name
        End If
    Next
    If numTables > 0 Then
        ReDim Preserve atables(1 To numTables)
    Else
        Erase atables
    End If

'code to extract FiledNames from recordset
    Dim fld As Field
    For Each fld In mrecRS.Fields
        combo2.Additem fld.Name
    Next

Let me know if you understand the idea - it's not to rename fields, but to map them and to use user's db 'as is'.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
DrDAuthor Commented:
To ameba

Yes, I think I understand the concept and I would like to try it out. I wonder if you could help me with an ADO query, instead of DAO. This is because I use ADO in the application and I am more familiar with it. Also, would you like to help me write code to preserve the User's field selections (re-mapped) so there is no further need to go through the combo box routine next time he accesses the database?

Thanks

David  
0
 
amebaCommented:
Hmm... I can try helping for MDB only and save field selections to registry only for one db:
Registry entries: DBName, Table, Field1, Field2, Field3, Field4
0
 
amebaCommented:
0
 
DrDAuthor Commented:
Yes!

That gives me a starting point! I think use of the registry is a good idea for storing the mapped fields, but I can't find the location of the created registry file.. Please let me know where the file is, and how to delete it!

I don't have any problem with using only .mdb databases. Have you any comments regarding Excel?

(points increased to 175.. Thanks for all the work!)

(you certainly know your way around VB!)

David
0
 
amebaCommented:
>registry file

There is no "registry file" which can be deleted.
We normally do not delete things from registry, even when uninstalling the software.
0
 
amebaCommented:
Excel is certainly different connection string.
0
 
DrDAuthor Commented:
To Ameba:

This is the part of your code I don't understand.. Please help!


Public Sub ReadIni()
    Dim i As Long
    On Error Resume Next
   
    ' user's db
    gDBName = GetSetting(App.Title, "DB", "DBName", "")
    gTable = GetSetting(App.Title, "DB", "Table", "")
    For i = 1 To gNumFields
        gFields(i) = GetSetting(App.Title, "DB", "Field" & i, "")
    Next
   
End Sub
0
 
DrDAuthor Commented:
Thanks muchly Ameba,

Best Regards and Bonne Chance!

David
0
 
amebaCommented:
Thank you, DrD!
0
 
FordraidersCommented:
Ameba,
Would you consider expanding on this question and using Excel spreadhseet with fixed headers as a target?
for points!
Thanks
fordraiders
0
 
amebaCommented:
Hi fordraiders,
sorry, I have never used Excel as 'data source'
0
 
FordraidersCommented:
Ameba,
I hope you don't mind if I repost and ask the question?
Thanks
fordraiders
0
 
amebaCommented:
I have no problem with that :-)
Feel free to use or post my code, if it helps.
0
 
FordraidersCommented:
Thanks
fordraiders
0
 
DrDAuthor Commented:
to fordraiders and ameba

I'd like to stay in the loop too!

Thanks

David
0
 
FordraidersCommented:
drd,
I have already repost the question.
Just give a response to get the emails...
fordraiders
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.