Increase ODBCTimeout property for all queries in an Access Database

If you ever have problems with Access queries timing out you can right-click in the query builder and set the ODBCTimeout property in the property window or you can run this code which will increase the ODBCTimeout property of all the queries in the database. You can just paste this code in a module and then run it and all the queries will have their timeout reset. I have some code that runs about 40 Access queries in a row and some of them were timing out and I found it easier to just change them all at once instead each one manually. Of course if you have too many queries timing out or if they still timeout after setting them to the maximum (6000) then you may need to convert you queries to SQL Server or whatever server database you are using. SQL Server is almost always faster than Access at running queries.



Dim qdfs As QueryDefs, qdf As QueryDef
Set qdfs = CurrentDb.QueryDefs
For Each qdf In qdfs
qdf.ODBCTimeout = 6000
Next

ContextMagic http://www.contextmagic.com/

Here's a free utility that I use all the time. It's called ContextMagic and you can get it for free here

http://www.contextmagic.com/



It adds some commands to the context menu when you right click over an icon or file. My favorite one is "Copy Name to Clipboard". It copies the full path to the item your mouse is over. I use it to email the location of files to people on the network and when I'm programming and need to reference a file path. I use it almost everyday.

Connect to Access from Excel using ADO

Here's the Excel VBA code I used to pull data from an Access query into an Excel Spreadsheet. It also brings in the column names. If you regularly have to pull data from Access or SQL server and then copy it to Excel this can help cut out some of those steps. I usually do a lot of formatting in code after bringing the data in, but left it out in this example in order to focus on the data extraction.

In order for this to work you need to add a refernce to ADO in Excel. In the Visual Basic Editor (VBE) go to Tools, References and search for Microsoft ActiveX Data Objects 2.8 Library. Click the checkbox and click OK.

Dim SQLcmd As String

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim fld As ADODB.Field

Dim Row As Integer

Dim Column As Integer

SQLcmd = "SELECT * FROM [AccessQueryName]"

rs.Open Source:=SQLcmd, _

ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=N:\MyFolder\MyDatabaseName.mdb" + _

"; User Id=admin; Password="

Column = 1

Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name

Cells(Row, Column).Select

With Selection.Interior

.ColorIndex = 15

.Pattern = xlSolid

End With

Selection.Font.Bold = True

Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs

Cells.Select

Cells.EntireColumn.AutoFit