Linked Tables Limit

[doco]

Is there a limit to the number of tables and Access 2007 db may link to?

TIA

 

[bparkinson]

Not a practical limit. I have 182 linked SQL Server tables and views in an Access 2010 database and it's all working fine.

http://blogs.office.com/b/microsoft-access/archive/2006/06/05/access-2007-limits.aspx

 

[doco]

Not a practical limit. I have 182 linked SQL Server tables and views in an Access 2010 database and it's all working fine.

http://blogs.office.com/b/microsoft-access/archive/2006/06/05/access-2007-limits.aspx

I did not believe there was a limit beyond local memory of course, but the reason I ask, is the [Link Tables] dialog in Access does not/will not show all the tables available in my SQL Server BE, in the listbox.

I can create additional tables in SQL Server and they do not show up either. Kind of frustrating...

 

[bparkinson]

I did not believe there was a limit beyond local memory of course, but the reason I ask, is the [Link Tables] dialog in Access does not/will not show all the tables available in my SQL Server BE, in the listbox.

I can create additional tables in SQL Server and they do not show up either. Kind of frustrating...

It's possible they are living under a different schema than dbo. Have you tried scrolling down past all the dbo tables? In one of my databases that's what happened, new tables are listed below dbo. This happened to me because I have to use SQL authentication - the database is far away in a strange land (Texas).

 

[bparkinson]

Oh yeah - you probably know this, but for the sake of completeness: When you add a table to SQL Server, it won't show up in Linked Table Manager until you do the initial linking of it to Access, under External Data | ODBC Database. This was mysterious to me and hard to discover when I first started with Access.

 

[doco]

Oh yeah - you probably know this, but for the sake of completeness: When you add a table to SQL Server, it won't show up in Linked Table Manager until you do the initial linking of it to Access, under External Data | ODBC Database. This was mysterious to me and hard to discover when I first started with Access.

I was going to External Data | ODBC Database. Still haven't figured out what the issue was. However, in my searching around I found a very cool tool from Microsoft that made life easier: Access Migration Assistant

Made life much simpler...

Thanks

 

[bparkinson]

Oh, yeah I'd forgotton about SSMA. It didn't work for me, but it looks great. I prefer to pull data from the SQL Server side rather than push it from Access, because I have loads of experience as a SQL Server guy, and much less in Access, and the SQL Server tool set is richer for ETL.

Hope Migration Ass't works for you.

 

[doco]

I actually built the original Access db more than ten years ago. As you may imagine the UI part of it has grown and changed over the years. I would far rather have the SQL Server powers as well. But did not have the time or the inclination to re-build something we have gotten very comfortable with; but do need to get the back end to something besides Access.

 

[bparkinson]

So are you going to SQL Server? Doesn't much matter when you built the UI, it will go to SQL OK. The advantages of having the back end in SQL Server are enormous and worth any learning curve.

 

[doco]

Yes, began the move today. Testing the rest of the week...