Connecting Excel to Access - DAO Error when trying to connect to Access (Error 3051)

1. SYMPTOMS
When an Excel VBA program trying to establish a connection from Excel to Access by using DAO library.  An error 3051 occur and says "The Microsoft Access database engine cannot open or write to the file...".  This error is occurred by random and same record may be able to write into the database successfully in the second run.  The following is the error message captured by my error handling procedure.

2. CAUSE
Basically this is a user right permission issue or you'll also get error 3051 if your program establish connection to the same database in second time.  Note that there is a little time required to close the database (especially if your database is large in size).  If you set up a loop for heavy reading or writing the database, there may be a chance for your program trying to establish a connection to the database before the previous session is closed completely.

3. RESOLUTION
Set up a wait time after closing the file and let the database connection closed completely before the next session.

The following procedure is used for executing an Action Query

Sub doActQry_DAO(sDB As String, sSQL As String)
    'Description: Execute an action query by using DAO
    'Date: 27-Jan-12
    'Copyright: www.CalvinSir.com
    'Remarks: Student version
    
    'initialization
    Dim db As DAO.Database
    Set db = OpenDatabase(sDB)
    
    'execute the SQL statment
    db.Execute sSQL
    
    db.Close
    Set db = Nothing
    '***************************************
    '*** set wait for 0.3 second to prevent Error 3051 ***
    Application.Wait (Now + TimeSerial(0, 0, 0.3)
    '***************************************
End Function

4. Further Study

How to connect Excel to an Access database?
How to connect Excel to MS-SQL?
How to program Excel VBA to connect database by using ADO or DAO?
How to write Action Queries?
How to write Sub-queries?
How to retrieve total number of records written into the database?
How to I tell if the action query has been executed successfully?
How to prevent crashing my program if I'm deleting a not existing database table?
How to prevent cashing my program if I'm reading an empty data table?
How to load a query results directly in an Excel worksheet

5. Finance VBA, C++ and SQL Training

We're financial experts to provide the following services:

1. Personal and company training on Finance VBA and SQL (both local & remote learning)
2. Financal modelling
3. Bloomberg plug-in and Excel VBA applications development
4. Database system development

Please contact us for more information.

6. Reference

Error message when you start Microsoft FRx: “Run-Time Error '3051': The Microsoft Jet Database Engine Cannot Open the File”

Custom Formula for Stock Analysis - Median Daily Trading Volume Percentage(%)

We can use Excel VBA to analysis stock data by using our own algorithm.  I'll share some of my custom formula in my blog, and you will find these functions are powerful tools to measure stock performance.  I'd like to start with a simple formula in this article - Median Daily Trading Volume.  It is a very useful information a) to identify if a stock's trade volume is high or low b) to allows you to make comparison of the trade volume between stocks.  This isn't a new concept and is similar to the ADTV in Investopedia.  I'm using Median in my formula instead of Average.

Daily Stock Volume% Applications

The following table is one of my daily report generated on 4-Mar-16.  The 1st column group the stocks by range.  The 2nd and the 3rd column are HSI constituents stock code and names.  The last column shows the daily volume%.  The second stock in the list CK Property(1113) has 264.5% volume% and that means traders in the market are in favor to this stock and the trade volume is 2.6 times higher than the average. The buy force is large and it pushes the stock price raise more than 4%.

Change Range		Stock Name	Volume%
>4%	494	Li & Fung	108.2%
>4%	1113	CK Property	264.5%
2% - 4%	4	Wharf (Hldgs)	127.5%
2% - 4%	11	Hang Seng Bank	208.0%
2% - 4%	12	Henderson Land	149.5%
2% - 4%	16	SHK Prop	175.3%
2% - 4%	17	New World Dev	177.5%
2% - 4%	83	Sino Land	197.2%
2% - 4%	135	Kunlun Energy	91.6%
2% - 4%	151	Want Want China	230.2%
2% - 4%	267	CITIC	112.0%
2% - 4%	322	Tingyi	120.2%
2% - 4%	688	China Overseas	145.2%
2% - 4%	836	China Res Power	92.8%
2% - 4%	883	CNOOC	186.4%
2% - 4%	992	Lenovo Group	170.2%
2% - 4%	2318	Ping An	153.6%
2% - 4%	2628	China Life	235.3%
2% - 4%	3328	Bankcomm	143.4%
0% - 2%	1	CKH Holdings	128.9%
0% - 2%	2	CLP Hldgs	91.1%
0% - 2%	3	HK & China Gas	126.4%
0% - 2%	5	HSBC Hldgs	99.8%
0% - 2%	6	Power Assets	111.7%
0% - 2%	19	Swire Pacific A	110.2%
0% - 2%	23	Bank of E Asia	99.9%
0% - 2%	27	Galaxy Ent	109.1%
0% - 2%	101	Hang Lung Prop	166.4%
0% - 2%	144	China Mer Hldgs	91.1%
0% - 2%	293	Cathay Pac Air	206.6%
0% - 2%	386	Sinopec Corp	126.8%
0% - 2%	388	HKEx	108.6%
0% - 2%	762	China Unicom	92.1%
0% - 2%	823	Link REIT	167.2%
0% - 2%	857	PetroChina	88.1%
0% - 2%	939	CCB	101.0%
0% - 2%	941	China Mobile	81.4%
0% - 2%	1088	China Shenhua	117.8%
0% - 2%	1109	China Res Land	121.3%
0% - 2%	1398	ICBC	118.1%
0% - 2%	1880	Belle Intl	71.5%
0% - 2%	1928	Sands China	93.5%
0% - 2%	2319	Mengniu Dairy	147.3%
0% - 2%	2388	BOC Hong Kong	86.3%
0% - 2%	3988	Bank of China	116.1%
0% - -2%	66	MTR Corporation	164.1%
0% - -2%	700	Tencent	123.1%
0% - -2%	1044	Hengan Intl	194.8%
0% - -2%	1299	AIA	111.6%
>=-4%	291	China Res Beer	261.1%

I convert the volume into percentage and make it easier for me to understand the trade volume size.  If I use the actual volume figure in this report, I'll find it harder to compare the volume between stocks.

Volume% Formula 

This formula use the latest trade volume compare to the Median of the latest 100 days volumes.  The following is my version of Trade Volume% formula:

Unlike ADTV, I use Median instead of Average due to the fact that some of the stocks may have extreme large or small volumes in some trade days.  Using Median may be more accurate to reflect the true volume.

SQL Statement for Calculating Volume%

All the stock history data are stored in the database table tblHistory and I use the following 2 SQL statements to calculate the Volume%.

This SQL statement is to copy Hong Kong Stock Exchange(388) latest 100 calendar days close prices into a data table tblClose100.

SELECT h2.Close INTO tblClose100

FROM tblHistory AS h2

WHERE h2.TradeDate>=

(

     SELECT Max(h1.TradeDate) 

     FROM tblHistory AS h1

) -100 AND 

h2.StockCode=388;

The 2nd SQL statement reads the data in the tblClose100 and then calculate the Median for a stock.

SELECT AVG(h1.Close) AS Median

FROM 

(

     SELECT h1.Close

     FROM tblClose100 AS h1, tblClose100 AS h2

     GROUP BY h1.Close

     HAVING 

          SUM(IIF([h1].[Close]<=[h2].[Close],1,0))>=COUNT(*)/2 AND 

          SUM(IIF([h1].[Close]>=[h2].[Close],1,0))>=COUNT(*)/2

)