Results 1 to 11 of 11
  1. #1
    Ghost is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Nov 2011
    Posts
    6

    Importing large excel file into multiple access files


    Hi everyone,

    I have a problem with importing file from excel. In one excel I have 10.000 rows and I would like to import this file into access, but I have to limit one mdb file to 1.000 rows per table.
    So I need 10 mdb files.
    Is this is possible without any visual code because I never use it?
    Thank you very much in advance for help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why must tables be limited to 1000 rows?

    I think this will be difficult to accomplish even with VBA.
    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
    Ghost is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Nov 2011
    Posts
    6
    Thank you for reply. My IT department demands maximum thousend rows per table. I really do not know the reason, but I know that in one file must be max 1000 rows.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Still not understanding the process. You said you must limit tables to 1000 rows and you have 10 mdb files. I think you meant 10 tables. Do you need to create a new database file with each import?

    What does IT have to do with managing database?
    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
    Ghost is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Nov 2011
    Posts
    6
    No. We are missunderstanding. I do not have 10 mdb files, but I have to create 10 mdb files with 1000 rows in table. I have to prepare this files for import on server.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You need 10 TABLES? Each table should have only 1000 rows? Do you need a new database file every time you perform this import procedure? Why should IT care how you manage database as long as you can give them the data?

    As stated, this will probably be tricky even with VBA. One possibility is to import the Excel sheet and allow Access to assign a sequential ID to each row. Then you can have query that pulls records by the row ID.

    SELECT * FROM linkedsheet WHERE ID Between 1 And 1000;

    SELECT * FROM linkedsheet WHERE ID Between 1001 And 2000;

    etc
    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.

  7. #7
    Ghost is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Nov 2011
    Posts
    6
    First of all, I have to thank you for help. Yes, you are right. I need a new database file for each import. I do not why they want to limit each file. I asume that they want to avoid congestion on the server.

    Thank you once again for suggestion. I will try to do with the mentioned query and I will inform you if I succeed.

  8. #8
    Ghost is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Nov 2011
    Posts
    6
    June7, here is what I have done.
    I have imported all 10000 rows in one mdb, then I did 10 copies of created mdb file. With your suggestion I did query

    DELETE *
    FROM linkedsheet
    WHERE ID Between 1001 And 10000;

    With this query I got one mdb with first 1000 rows in the table.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So you have a satisfactory solution?
    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
    Ghost is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Nov 2011
    Posts
    6
    Temporeraily yes, but this is not solution if I would have e.g 50000 rows, because I will lost to many time. How many would be conplicated with VB?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    VBA could automate much of the process.

    You really should find out why IT has this limitation. It seems very odd to me.
    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.

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

Similar Threads

  1. Need help exporting large Access file into Excel
    By phidelt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 06:14 PM
  2. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  3. Replies: 7
    Last Post: 12-09-2012, 06:20 PM
  4. Replies: 6
    Last Post: 12-03-2012, 08:08 AM
  5. Importing large txt file into Access 2010
    By Jimbo in forum Import/Export Data
    Replies: 6
    Last Post: 06-30-2011, 08:26 PM

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