After-a-long time I got chance to work on MS-Access. MS-Access is very intresting database-application development tool. I like ms access a lot
becouse, this is the tool that helped me to turn into database in 2009.
Microsoft Access is a popular database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. Microsoft Access stores data in its own format based on the Access Jet Database Engine.
It can also import or link directly to data stored in other applications and databases. Now a days there are lots of changes in Access but basics are still the same.
I received my New task to migrate Access database into sql server with proper structure and data. Today I am sharing some Access SQL Script script which helped to migrate database into SQL Server.
How to list of all tables from msaccess database ?
List of tables with rows count from msaccess database ?
SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6))) order by MSysObjects.Name
SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6)) AND ((MSysObjects.Flags)=0)) order by MSysObjects.Name
SELECT [Name], DCount("*", vTbl.[Name]) As TtlCount FROM MSysObjects As vTbl
WHERE [Type] IN (1,4,6) And [Name] Not Like "MSys*" And [Name] Not Like "~*" ORDER BY [Name]
Thanks for reading
becouse, this is the tool that helped me to turn into database in 2009.
Microsoft Access is a popular database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. Microsoft Access stores data in its own format based on the Access Jet Database Engine.
It can also import or link directly to data stored in other applications and databases. Now a days there are lots of changes in Access but basics are still the same.
I received my New task to migrate Access database into sql server with proper structure and data. Today I am sharing some Access SQL Script script which helped to migrate database into SQL Server.
How to list of all tables from msaccess database ?
List of tables with rows count from msaccess database ?
SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6))) order by MSysObjects.Name
SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6)) AND ((MSysObjects.Flags)=0)) order by MSysObjects.Name
SELECT [Name], DCount("*", vTbl.[Name]) As TtlCount FROM MSysObjects As vTbl
WHERE [Type] IN (1,4,6) And [Name] Not Like "MSys*" And [Name] Not Like "~*" ORDER BY [Name]
Thanks for reading
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757