Solved

Strategy for getting data from Access and Excel databases

Posted on 2002-04-14
19
208 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:DrD
  • 10
  • 5
  • 4
19 Comments
 
LVL 15

Expert Comment

by:ameba
Comment Utility
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
 
LVL 15

Expert Comment

by:ameba
Comment Utility
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
 

Author Comment

by:DrD
Comment Utility
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
 
LVL 15

Expert Comment

by:ameba
Comment Utility
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
 
LVL 15

Expert Comment

by:ameba
Comment Utility
0
 

Author Comment

by:DrD
Comment Utility
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
 
LVL 15

Expert Comment

by:ameba
Comment Utility
>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
 
LVL 15

Expert Comment

by:ameba
Comment Utility
Excel is certainly different connection string.
0
 

Author Comment

by:DrD
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Accepted Solution

by:
ameba earned 175 total points
Comment Utility
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
 

Author Comment

by:DrD
Comment Utility
Thanks muchly Ameba,

Best Regards and Bonne Chance!

David
0
 
LVL 15

Expert Comment

by:ameba
Comment Utility
Thank you, DrD!
0
 
LVL 3

Expert Comment

by:fordraiders
Comment Utility
Ameba,
Would you consider expanding on this question and using Excel spreadhseet with fixed headers as a target?
for points!
Thanks
fordraiders
0
 
LVL 15

Expert Comment

by:ameba
Comment Utility
Hi fordraiders,
sorry, I have never used Excel as 'data source'
0
 
LVL 3

Expert Comment

by:fordraiders
Comment Utility
Ameba,
I hope you don't mind if I repost and ask the question?
Thanks
fordraiders
0
 
LVL 15

Expert Comment

by:ameba
Comment Utility
I have no problem with that :-)
Feel free to use or post my code, if it helps.
0
 
LVL 3

Expert Comment

by:fordraiders
Comment Utility
Thanks
fordraiders
0
 

Author Comment

by:DrD
Comment Utility
to fordraiders and ameba

I'd like to stay in the loop too!

Thanks

David
0
 
LVL 3

Expert Comment

by:fordraiders
Comment Utility
drd,
I have already repost the question.
Just give a response to get the emails...
fordraiders
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now