Page 1 of 4 123 ... LastLast
Results 1 to 15 of 53
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Import - Access (10.4)

    Any where I can go to study moving data from other sources like Excel, Word into Access programatically?

    Thanx.

    Kim
    Thanks

    Kim

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import - Access (10.4)

    There are at least several of ways to do that sort of thing. One approach is the transfer set of commands for Excel and for Text. In that approach you basically take the data and create a table from it. Another approach is to use Automation from the application to take data in Excel or Word and actually store it in an Access database. Then of course the current versions (which you are using) can execute ADO or DAO commands to read and write Access tables, and finally there is XML. With that many choices, the right one is usually dictated by the situation and what you are trying to accomplish. One thing to remember however, is that Access isn't very good at storing formatted text, whether it comes from Word or Excel - about the only choice you have is to store binary data in a memo-like field. Of course you could store a Word or Excel document in the Access table as an OLE Object. Hope this helps more than it confuses. If you want to explore a specific process, post back and one of us will try to elaborate.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import - Access (10.4)

    Thanx for the answer. Guess it mite help to let you know what I am trying.

    Am trying to import data into Access using a VB interface. Presently, I am working on importing data from an Access table into another Access table. The 'from' table will not have the same number and names of fields that the 'to' table has. The user supplies the names of his fields that are to be imported.

    The problem I am having is that if the field does not exist in the 'to' table, I get a "item can not be found......' msg. Can't seem to figure out how to query the table and if the field does not exist, move on to the next field. Here is part of my code:

    With rsImport
    If .EOF Then
    If .BOF Then
    Exit Sub
    End If
    Else
    Do Until .EOF
    nImport.Firstname = rsImport.Fields(sFirst) & ""
    nImport.Lastname = rsImport.Fields(sLast) & ""
    nImport.Address = rsImport.Fields(sAddress) & ""
    nImport.City = rsImport.Fields(sCity) & ""
    nImport.State = rsImport.Fields(sState) & ""
    nImport.Zip = rsImport.Fields(sZip) & ""
    nImport.Country = rsImport.Fields(sCountry) & ""
    nImport.ClassYear = rsImport.Fields(sClassYear) & ""
    nImport.Phone = rsImport.Fields(sPhone) & ""
    nImport.Fax = rsImport.Fields(sFax) & ""
    nImport.MaidenName = rsImport.Fields(sMaidenname) & ""
    nImport.Spouse = rsImport.Fields(sSpouse) & ""
    nImport.eMail = rsImport.Fields(sEmail) & ""
    nImport.BirthDate = rsImport.Fields(sBirthdate) & ""
    .MoveNext
    Loop
    End If
    End With
    End Sub

    Hope there are some ideas to work around this dilemma. thanx.

    Kim
    Thanks

    Kim

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import - Access (10.4)

    Hans has given you good advice - if you are doing this repeatedly, an action query would be a far better choice. And you aren't really importing at this stage - you are copying data from one table to another. A make table query would certainly be an easier task that trying to all this with TableDefs and what not. You could actually use a form to have the user define the new table field names and build and execute the SQL on the fly. That way you wouldn't need to learn all about DAO or ADO, and the entire object model of Access.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import - Access (10.4)

    Hans,

    I don't know what you mean by 'production'. I am working with Access using a VB interface.

    At any rate, I have some questions about the function(s) that you wrote. Placed the function in my program:

    Function FieldExists(rst As Recordset, sfieldname As String) As Boolean
    Dim fld As Field
    For Each fld In rst.Fields
    If fld.Name = sfieldname Then
    FieldExists = True
    End If
    Next fld
    Set fld = Nothing
    End Function

    Called it with a public sub:

    Public Sub ImportAccessGetData(ByVal sDatabaseName As String, ByVal sTableName As String, _
    ByVal sFirst As String, ByVal sLast As String, _

    set the variables to a instance of the Import class nImport:

    nImport.Lastname = slast

    Do Until .EOF
    If FieldExists(rsImport, sLast) Then
    nImport.Lastname = rsImport.Fields(sLast) & ""
    iCol.Add nImport
    .MoveNext
    MsgBox sLast
    Loop

    looped through all of the fields adding to a collection after each pass. But, the msgbox gives me the name of the field and not the contents. I gotta be close but am too close to the forest .....

    Thanks for the help.

    Kim
    Thanks

    Kim

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

    Re: Import - Access (10.4)

    1. With my remark about 'production' vs. 'programming exercise' I meant to say that importing or copying data this way is slow. If you want to learn how to program ADO or DAO, that is not a problem - it is an interesting challenge. But if you're going to use this on tables with many fields and many records, you'll quickly find out that performance is not speedy. Executing SQL statements is generally much faster than looping through a recordset.

    2. About your code:

    In the function FieldExists, you should insert Exit For after the line FieldExists = True, as in my example. This avoids continuing the loop after the field name has already been found, thus making execution slightly faster.

    If I read your code correctly, sFirst and sLast are the names of first name and last name fields in a table. These will not change. MsgBox sLast will display the field name every time. If you want to display the field value, use MsgBox nImport.LastName or MsgBox rsImport.Fields(sLast).

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import - Access (10.4)

    Hans,

    Many thanks for the help. Where do I go to find out how to do this process faster? The process that I am trying to use is definitely not very fast.

    In the FieldExists function, fld.name = sfieldname is case sensitive. Is there a way to make it not case sensitive? Msgbox rsimport.fields(slast) gives me the data in the field. Is it save to assume then that the field data is collected in iCol?

    Also, when I try to place all of this data into the new database, it seems to a long and arduous process. First of all I find that the variables have to variants in this type of For Each:

    For Each sLast in iCol

    sSql = "INSERT Into Members(Lastname) VALUES( '" & slast & "')"
    next

    Is there a better way to add all of the gathered data at one time?

    Thanks.

    Kim
    Thanks

    Kim

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

    Re: Import - Access (10.4)

    I hopy you will forgive me for asking, but is this meant for "production", or as an exercise in VB programming? In some circumstances, looping through a recordset is the best solution, but in general SQL action queries are much more efficient. If necessary, you can write the SQL on the fly in code.

    To find out what fields a recordset has, inspect its Fields collection. There are (at least) two ways to check for the existence of a field with a certain name:

    1. Loop through the fields:

    Function FieldExists(rst As Recordset, strFieldName As String) As Boolean
    Dim fld As Field
    For Each fld In rst.Fields
    If fld.Name = strFieldName Then
    ' Gotcha!
    FieldExists = True
    Exit For
    End If
    Next fld
    Set fld = Nothing
    End Function

    2. Trap error in referring to field

    Function FieldExists(rst As Recordset, strFieldName As String) As Boolean
    Dim strDummy As String
    On Error Resume Next
    ' Try to refer to field
    strDummy = rst.Fields(strFieldName).Name
    ' Field exists if no error occurred
    FieldExists = (Err = 0)
    End Sub

    P.S. I don't know what nImport is, but in your code, you loop through rsImport and repeatedly assign values without saving them; as far as I can see, you are overwriting the values over and over.

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

    Re: Import - Access (10.4)

    I'll answer your question about comparing field names first. Put the following line at the top of the module containing the FieldExists function:

    Option Compare Database

    (if there is a line Option Compare Binary, replace it with Option Compare Database)

    This tells Access to compare strings using the sort order set for the database; this usually ignores case differences and diacritics (accents).

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

    Re: Import - Access (10.4)

    I would go about it this way:
    1. <LI>Display a list of fields in the "From" table. You can use an unbound list box for this, with Row Source Type set to List of Fields, Row Source to the name of the "From" table, and MultiSelect to Single or Extended.
      <LI>Use the FieldExists function to check whether the fields (in the "From" table) selected by the user exist in the "To" table. It's up to you to decide to do with this - abort the transfer if one of the fields doesn't exits in the target, or ask the user whether to skip the field and continue.
      <LI>Construct an SQL string that transfers the appropriate fields from all records.
    Here is some air code to do that; it assumes that the field names to be transferred have been selected in a multiselect list box named lstFields and have been checked for existence.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim strSource As String
    Dim strTarget As String
    Dim strFieldList As String
    Dim strSQL As String
    Dim varItem As Variant

    strSource = ... ' name of "From" table goes here
    strTarget = ... ' name of "To" table goes here

    strFieldList = ""

    ' Construct list of fields to transfer, enclosed in square brackets
    For Each varItem In lstFields.ItemsSelected
    strFieldList = strFieldList & ",[" & lstFields.ItemData(varItem) & "]"
    Next varItem

    If strFieldList = "" Then
    ' Nothing selected - get out
    MsgBox "No fields selected"
    Exit Sub
    End If

    ' Get rid of first comma
    strFieldList = Mid(strFieldList, 2)

    ' Construct SQL
    strSQL = "INSERT INTO [" & strTarget & "] (" & strFieldList & ") " & _
    "SELECT " & strFieldList & " FROM [" & strSource & "]"

    ' Execute SQL
    CurrentDb.Execute strSQL

    <img src=/w3timages/blueline.gif width=33% height=2>

    You'll have to adapt this code for your own use.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import - Access (10.4)

    Hans,

    Thanks for your help. Presently, I am digesting the info you supplied. I'm sure I'll be back.

    Kim
    Thanks

    Kim

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import - Access (10.4)

    Hans,

    If I display the fields in a lstbox for the 'from' table they surely will not match up with the names of the fields in the 'to' table. Two different people are supplying the databases. So, if i sue your FieldExists function, it seems that it will always be false. Could work around this by having the user map the fields, matching his/her fields with those supplied in the 'to' table.

    In VB there is no lstfields.itemsselected. At least I could not find it. There is a .selected(integer) or .text. Have to use an object or variant in a For each/ Next loop in VB.

    Does the strSQL, INSERT one record at a time using all of the fields selected with lstFields?

    Thanks.

    Kim
    Thanks

    Kim

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

    Re: Import - Access (10.4)

    1. About matching field names:

    From
    <hr>The user supplies the names of his fields that are to be imported. The problem I am having is that if the field does not exist in the 'to' table, I get a "item can not be found......' msgbox<hr>
    I had assumed that the user selects fields from the source table, and that their names would match those in the target table, except if the field didn't exist in the target. If the names don't match, letting the user select fields from the source is not enough - you have to provide a way to match them to fields in the target. Once the user has done that, it doesn't make sense to check whether the field exists in the target. So your earlier info seems misleading to me - unless I misread it (which is quite possible <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, see below).

    2. About ItemsSelected:

    Here I did misread your post - sorry. I mistakenly assumed that by VB you meant VBA, but upon rereading it's clear that you meant VB6. ItemsSelected is a property of the list box native to Access, which is different from the list box in VB6, so you can't use it in VB6. You should be able to use

    Dim i As Integer
    For i = 0 To lstFields.ListCount - 1
    If lstFields.Selected(i) = True Then
    ...
    End If
    Next i

    3. About the SQL statement:

    INSERT INTO tblSomething (Field1, Field2, ..., Fieldn) SELECT FieldA, FieldB, ..., FieldN FROM tblOther WHERE ...

    will append ALL records from tblOther that satisfy the WHERE condition to tblSomething in one run. That's why it is much more efficient than looping through a recordset and appending records one by one.

  14. #14
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import - Access (10.4)

    Hans,

    Certainly did not intend/want to mislead you.

    Since I have the user entering his/her fields to transfer, about the only thing I need now is the sql statement? And accordingly, if I want all records from the source table to be transferred, do I need a WHERE statement?

    Also, I added a VALUE(SELECT ....) and all that did was to enter the fieldname in the target table. Therefore, seems that I need to collect the data from the source table and then do an insert? Or did I miss something?

    Thanks.

    Kim
    Thanks

    Kim

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import - Access (10.4)

    Why don't you post your INSERT statement here for all to see.

    Pat

Page 1 of 4 123 ... LastLast

Posting Permissions

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