How does Access Work?

thes4s67

Board Regular
Joined
Aug 17, 2015
Messages
186
Hello all,

I've never used MS Access before but I know it handles database stuff. (I think) Well if it does, I was wondering is it possible to do some work in Excel and then import it to the MS Access DB which will update the actual SQL database? Essentially right now, I do some work in Excel then I import it manually via a web interface. Can I further automate this using a combination of Excel and Access or should I just learn how to connect to my SQL database with VB in Excel itself?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
For what it's worth, you can also use ADO to connect Excel to MySQL databases if you have an ODBC provider for it - just the same way you would connect to MSAccess or SQL server (if you are into writing VBA code for your data access).
 
Upvote 0
For what it is worth, MS Access has a Linked Table toolused widely in the industry.

A Linked Table can be set directly to Excel. It is up to the Excel user tounderstand the concepts of database. An example might be a primary key columncan't have duplicates. The Linked Table from Access allows fantastic data-typeconversion.
The linked table using SQL Server Native Client (a free ODBC down load) allowsAccess to use SQL Server including a free web version known as Azure.
Access provides an excellent link between Excel and SQL Server that includesdata type conversion assistance.

In a recent task, I was able to write code in MS Access that remotely searched,catalogued, and harvested data from over 3,000 Excel workbooks with varyingamount of worksheets (3 to 9 each worksheet with around 20,000 rows), located at dozens of network locations, and migrate the data into SQL Server.
This provided a full audit trail for a regulatory organization that desired toupgrade from Excel to SQL Server.
Each of the workbooks / worksheets were opened and edited to indicate to the next user what date and where to find the data.
Then the workbook was locked to encourage the users to use the new data system.
The SQL Server also supports Linked Serversto Oracle Databases for real-time data sharing across the data enterprise.

Unlike MySQL, Access and Excel share a common programming lanugage and abilityto communicate across COM object models.
SQL Server and MSAccess have advantages for larger data sets when combined withSQL Server Native Client.
For example: a query in MS Access using the SQL language including a filter(s)will be converted into an efficient T-SQL executable vial ODBC that utilizesthe SQL Server Execution Plan. While my Excel conversions may only consist of afew gigs of data, the business rules for regulations layer require efficiency.

Once data is migrated into Excel and the business rule layer is able to providethe business metadata, the Excel Object Model can also be used for efficientdata mining, reporting, and other ad-hock business requirements.

A common trend is to place MSAccess on Citrix. This provides rich web styleuser interfaces that can be used on any device from Apple, Android to Windowssecurely and with a very small bandwidth footprint.
<o:p></o:p>
 
Upvote 0
Then the workbook was locked to encourage the users to use the new data system.

:cool: I'll have to try that someday!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top