Results 1 to 14 of 14
  1. #1
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22

    Create table with fields based off import spec? / How to deal with changing import data


    Hey guys,

    I am not sure if that's even possible but it never hurts to ask.

    I have a VBA code that imports external data with an import spec. It works fine.
    But if the data is changed e.g. more/different fields in the spreadsheet, then I have a problem. I thought by updating the import spec would solve the problem but it will give me an error and won't update the table fields.

    So I have to re-create the table all over again but I don't want to do it through the wizard and want to have a code that will create a table (if it doesn't exist) and set up the various fields based off an import spec?

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,077
    A process that routinely modifies db structure is usually not a good idea.

    I have never used Saved Import Specification, but then I don't have to routinely import data.

    VBA is more flexible but of course requires more programming to emulate what the wizards handle.

    That is the nature of Specification, nothing can change. The sheet structure must be static.

    Why does the sheet structure change?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    The sheet struture is pulled off OBIEE and I just want to account for situations when the user modifies fields to the OBIEE analysis and exports the extract. Trying to make it as flexible and user-friendly as possible. If the columns don't match, the table has to be recreated and I'd like to automate it instead of using wizard function. Set up the table and automatically format the fields( names, types) and import the spreadsheet per the import specifications. Do you think it's possible?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,077
    Won't be simple. How will you know the datatype of the 'new' fields? The wizard handles this by reviewing cell content and making a best fit choice of datatype - doesn't always get it right.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    aah, that's a good point. I think the safest way is to give instructions to follow the wizard, update and save the import specs as needed and create the table. Well, it's only a couple of clicks. I guess I am trying to be too fancy with the code, LOL. thanks!

  6. #6
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    Well, it turns out that if access will only show error if the import file has more fields than the existing Access DB. If however, the import file has fewer fields, it won't show anything and just import the columns from left to right and it's wrong. How do I validate for such error?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,077
    Think that has been answered as best I know. Don't use import specification. Use VBA to open and manipulate Excel objects.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    I don't want to overcomplicate it and already planned to provide instructions how to use the wizard and updating import specs or adding new columns manually. The reason I am asking is because I will not be the one using it on daily basis and there will be multiple users who will be pulling the report from the same source. What if one person changes it, and the other doesn't know. Then the second person will try to import the extract, possibly replacing (deleting all records) and messing up the database. I want to use VBA to validate that the fields / columns match and if not, then make the corrections as instructed in the manual. Hope this makes sense.

    (btw feel free to move this post to import/export data section as it's less programming i guess)

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,077
    So you would still need VBA that opens and manipulates Excel objects to read the header row in the spreadsheet.

    For ideas review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    So I am trying to open Excel object and count columns but I keep getting Run time error 1004. Based on google, this code should be working?

    Code:
    Sub Validate_Columns(Filepath1 As String, StrName As String, TableName As String)
    Dim xlApp As Object
    Dim xlWS As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open (Filepath1 & StrName), True, False
    Set xlWS = xlApp.ActiveWorkbook.Worksheets(1)
    Dim lastColumn As Long
    With xlWS
    lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox lastColumn
    Set xlApp = Nothing
    End Sub

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,077
    Based on what in Google?

    What is the full error message?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    I looked up the code on http://www.rondebruin.nl/win/s9/win005.htm and just changed variables accordingly to my strings.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	7 
Size:	10.0 KB 
ID:	23507Click image for larger version. 

Name:	Capture2.PNG 
Views:	7 
Size:	13.6 KB 
ID:	23508

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,077
    I don't have much experience coding within Access to manipulate Excel but when I have done it, I declare objects for the application and workbook and then maybe also the worksheet. Demonstrated by Ken Snell example http://www.accessmvp.com/KDSnell/EXC...m#WriteFileRst

    The example from the other site is code behind Excel and doesn't even show these object declarations because they aren't needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    caubetiep1337 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    22
    I made it work with using "UsedRange" instead of .Cells.

    Code:
    With xlWS
    lastColumn = .UsedRange.Columns(.UsedRange.Columns.Count).Column
    MsgBox lastColumn

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 12-28-2015, 07:51 AM
  2. My import spec skips first record
    By Mikey1987 in forum Access
    Replies: 2
    Last Post: 12-01-2015, 06:49 AM
  3. Import spec saved
    By crowegreg in forum Import/Export Data
    Replies: 6
    Last Post: 02-17-2014, 04:11 PM
  4. Import spec for importing Excel2010 into Access 2010
    By crowegreg in forum Import/Export Data
    Replies: 0
    Last Post: 08-02-2013, 12:18 PM
  5. Choose Import Spec from option group
    By thart21 in forum Import/Export Data
    Replies: 4
    Last Post: 06-29-2011, 10:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums