Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 30th, 2007, 01:55 PM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Column names! Help!

I'm making an Access 2003 database to catalog a CD/Record collection as a favor for a DJ friend of mine. I've managed the database easily enough...but I've been told that he now wants to be able to import collection data supplied by his DJ buddies. I guess they all use Excel spreadsheets to catalog their collections, but the column labels aren't the same and the columns aren't arranged the same from person to person. Kind of like:

   C1 C2A C3 C4B = C2B C4A C3 C1
R1 R1
R2 R2
R3 R3

Where C2A/C2B are different but similar names for the same field of data while C4A/C4B are different names for the same field but maybe with no similarity. The differences in names may be as simple as a space or dash, or may be a different label completely. The row data should be pretty much standard across all of the sheets, though. Artist names, track lengths, years, etc. are the same data types and formats across all of the different inputs (or so I'm told).

What I'd like to do is somehow write some VBA to either rename each field name based on the data in the column (or something like that) or somehow do it so that if the column name is like my database column name (match a % of the character sequence) that it will be renamed. The latter option may not be as useful...but beggers can't be choosers. I'll be satisfied with whatever I can do.

I'm at a total loss as to how to go about this. I have minimal VBA experience, basically limited to the exercises in WROX Beginning Access 2003 VBA and O'Reilly Access Database Design & Programming. This is becoming a pain...but I'm sure it must be something that's already been considered by someone on the forum.
Reply With Quote
  #2 (permalink)  
Old May 30th, 2007, 03:26 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You might try using the Mid Function to find if part of the column name is the same.
Not sure about importing data with different column names. Its hard to tell the data where to go if you don't know what the column will be called.


Reply With Quote
  #3 (permalink)  
Old May 30th, 2007, 03:55 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Alright, I have an answer for you, and although it works, it isn't pretty!!

There are two approaches, this one gives you maximum control, while the other is able to be automated, but you really need to know all possible field names, or unique parts of field names.

1. Make sure the Excel file has the field names on the first row.

2. Put in this code - txtImportFile is a field with the full file name in. This also will need a blank table called tblTemp to delete the first time around, although you could also find some code that deletes it, only if it already exists.

Code:
    Dim cn As ADODB.Connection

    Dim strSQL As String

    Set cn = Application.CurrentProject.Connection

    strSQL = "DROP TABLE tblTemp"

    cn.Execute strSQL

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", 
    txtImportFile, False


3. Now tblTemp has your spreadsheet loaded into it. The next step is to open another form that has combo boxes set up for Artist Name, Album Name etc. When this form is loading, use the code below in the Form_Load module to populate each of the combo boxes with a list of possible fields.

Code:
    On Error Resume Next
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim valueList As String

    Set cn = Application.CurrentProject.Connection
    Set rs = New ADODB.Recordset

    strSQL = "SELECT * FROM tblTemp"

    rs.Open strSQL, cn

    If Not (rs.EOF And rs.BOF) Then
        i = 0
        valueList = vbNullString
        Do While err.Number = 0
            valueList = valueList & Chr(34) & rs.Fields(i) & Chr(34) & ";" & i & ";"
            i = i + 1
        Loop
        numfields = i - 1
        Me.CDArtist.RowSource = valueList
        Me.CDName.RowSource = valueList
    End If

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing


4. The user can now select the matching field from each of the combo boxes. Finally have a load button they can click, and then execute a check to make sure each combo box has been selected. (All fields are mandatory) The run the following code:

Code:
    On Error GoTo importError
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim valueList As String
    Dim strCDName As String
    Dim strCDArtist As String

    Set cn = Application.CurrentProject.Connection
    Set rs = New ADODB.Recordset

    strSQL = "SELECT * FROM tblTemp"

    rs.Open strSQL, cn

    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        rs.MoveNext

        While rs.EOF = False
            If IsNull(rs.Fields(Val(Me.CDName))) Then
                'This means there is a blank line!!
                rs.MoveNext
            Else

                strCDName = rs.Fields(Val(Me.CDName))
                strCDArtist = rs.fields(Val(Me.CDArtist))

                strSQL = "INSERT INTO tblCollection ( CDName, CDArtist ) VALUES ("
                strSQL = strSQL & chr(34) & strCDName & chr(34) & ", "
                strSQL = strSQL & chr(34) & strCDArtist & chr(34) & ")"

                cn.Execute strSQL
            End if
        Wend
    End if

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing

importError:
    MsgBox err.Number & " - " & err.Description
    Resume Next
And that should do it, although it will need a little adapting, and I have probably mistyped a few things, but hopefully that'll get you on the right track.

Mike

Mike
EchoVue.com
Reply With Quote
  #4 (permalink)  
Old May 30th, 2007, 04:26 PM
Registered User
 
Join Date: May 2007
Location: Bradford, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How many excel spreadsheets are there? It would be straightforward enough to manipulate the spreadsheet before importing them, provided there aren't too many.


Reply With Quote
  #5 (permalink)  
Old May 31st, 2007, 09:48 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That was my thought, AndrewHutch. I would just rename all the columns, and then do the import.

Alternatively, import them all into their own tables, and then run an append query on each one to the main table, which gives you the option to speficy the receiving column.

mmcdonal
Reply With Quote
  #6 (permalink)  
Old May 31st, 2007, 01:56 PM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How about this, could I create a form that displays the column headers from the import spreadsheet as label captions or textboxes and have my buddy select the database field names from a series of combo boxes that match up with them. Then have a command button to activate a series of queries to rename the fields in tblTemp, which would then allow for the copying of data into the appropriate tables? I've made an attempt at this, but I can't figure out how to reference the tblTemp field names/column names as a variable in the form combobox VBA. The database is done using ADO...which appears to make this more difficult from what I've seen. Can one of you guys lend me some brainpower on this? I'd tell the guy to just rename the columns in the spreadsheets (50 or so), but I owe him a favor.
Reply With Quote
  #7 (permalink)  
Old June 1st, 2007, 06:37 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Unless the guy has thousands of buddies, and he is going to be constantly importing these files, I would just have him do this (ETL) in Excel to match column names, and do the import from there. Normally you program to make repetitive tasks easier, but it doesn't sound like this is repetitive enough for that.


mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic column names in crystal reports pankaj_daga Crystal Reports 3 January 19th, 2009 02:55 AM
Displaying Table Column/Fields names in a listbox Durkee VB.NET 2002/2003 Basics 4 September 25th, 2007 03:37 PM
show just the column names from a MySQL command crmpicco MySQL 5 December 7th, 2006 08:34 AM
how to Retrieve Column Names Using SQL Query saravananedu Oracle 2 September 10th, 2005 01:57 AM
Query for Column Names and Datatypes rstelma SQL Server 2000 3 August 23rd, 2005 02:52 PM



All times are GMT -4. The time now is 05:34 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.