Solved

Strategy for getting data from Access and Excel databases

Posted on 2002-04-14
19
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 4
19 Comments
 
LVL 15

Expert Comment

by:ameba
ID: 6941409
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
ID: 6941452
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
ID: 6942987
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 15

Expert Comment

by:ameba
ID: 6944499
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
ID: 6945589
0
 

Author Comment

by:DrD
ID: 6945754
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
ID: 6946137
>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
ID: 6946163
Excel is certainly different connection string.
0
 

Author Comment

by:DrD
ID: 6946217
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
 
LVL 15

Accepted Solution

by:
ameba earned 175 total points
ID: 6946257
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
ID: 6947043
Thanks muchly Ameba,

Best Regards and Bonne Chance!

David
0
 
LVL 15

Expert Comment

by:ameba
ID: 6947256
Thank you, DrD!
0
 
LVL 3

Expert Comment

by:fordraiders
ID: 7529729
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
ID: 7529878
Hi fordraiders,
sorry, I have never used Excel as 'data source'
0
 
LVL 3

Expert Comment

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

Expert Comment

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

Expert Comment

by:fordraiders
ID: 7531586
Thanks
fordraiders
0
 

Author Comment

by:DrD
ID: 7532208
to fordraiders and ameba

I'd like to stay in the loop too!

Thanks

David
0
 
LVL 3

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 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