none
Why is this happening?

    Frage

  • I have a table created in an SQL Server database.

    I created a DSN to connect to the server and database.

    When I select the table to load data into access from, using this connection, it starts to import the rows, but then after a while I get this message instead:

    "Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.

    There are 1.25 Million rows in this database.  It is an SQL Server table in an SQL Server database.  It is not 'corrupt'.

    This doesn't seem like a particularly useful message to display between MICROSOFT SQL SERVER and MICROSOFT ACCESS.

    Anyone have any ideas what might be going wrong passing the data to Access?  This is Access 2013, it ought to be able to handle this amount of data!

    Thanks in advance.

    Montag, 19. August 2013 20:33

Alle Antworten

  • What exactly happens here: "it starts to import the rows, but then after a while..."?

    Are any records loaded?  If so, does the process always fail at a specific record (or a certain time)?  There might be a type incompatibility where SQL Server has accepted a piece of data that Access cannot process.

    Try limiting the recordset different ways to locate the problem (e.g. Year = 2012 or ID < 1000) and confirm the process works.

    Montag, 19. August 2013 21:51
  • What is your overall objective? It seems to be you want to copy data from a SQL Server table to an Access table? Why?


    -Tom. Microsoft Access MVP

    Dienstag, 20. August 2013 03:27
  • Apparently the problem is that Access, unlike the rest of Office 2013 products, cannot load more than 65K rows?

    Here's the short and simple response for any Office product manager that may be looking here.

    For many years now, people at Microsoft have asserted that Office tools are great front ends for SQL server; now Office is being positioned as part of Microsoft's BI stack.

    If you want Office to be taken seriously in these roles, you're going to need to be able to load millions of rows of data.  Otherwise you won't be taken seriously.

    My company's databases are modest in size compared to some, yet Access is choking on a 1.7 million rows?!!?

    This is NOT a serious tool for dataset manipulation.

    Mittwoch, 4. September 2013 00:36
  • Apparently the problem is that Access, unlike the rest of Office 2013 products, cannot load more than 65K rows?

    This is not true.  Access has no record count limit.  It does have a filesize limit -- the database file can't exceed 2GB.

    Here's the short and simple response for any Office product manager that may be looking here.

    For many years now, people at Microsoft have asserted that Office tools are great front ends for SQL server; now Office is being positioned as part of Microsoft's BI stack.

    If you want Office to be taken seriously in these roles, you're going to need to be able to load millions of rows of data.  Otherwise you won't be taken seriously.

    My company's databases are modest in size compared to some, yet Access is choking on a 1.7 million rows?!!?

    This is NOT a serious tool for dataset manipulation.

    Access works as a fine front-end for a large SQL Server back-end, but it does it by *linking* to the data, not by importing it.  If you try to *import* the data, thus copying it into the Access database file, you may well run into the file-size limit.  The answer is not to try to import it, but to link to the SQL server tables and views.

    That's why Tom van Stiphout asked in response to your original post, "It seems to be you want to copy data from a SQL Server table to an Access table? Why?"


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Mittwoch, 4. September 2013 03:42
  • row count / filesize in practical terms it is still a limitation that isn't very practical;

    as to linking, my end user tried that - every cell comes back with some sort if hashed-mark gibberish.

    Doesn't matter.  We figured out another way; which is the real point here.

    Microsoft has been dancing around the issue of being able to provide something to the end user to consume data and it always seems to net down to 'you're not doing it right'.

    How about instead the product developer does it right and makes a simple wizard for an end user to be able to consume the data?

    Mittwoch, 4. September 2013 21:12
  • I'm not sure what your end-user did; there are some specific issues that can arise when connecting to SQL Server, but they can generally be handled.  I'm inclined to think you gave up too soon, but that's your prerogative.  However, there are probably hundreds of thousands of Access applications out there, happily connected to SQL Server.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Mittwoch, 4. September 2013 21:21