Access Programming in VBA

Converting Number to Strings and String to Number, Formating

dim nAmount as currency, s as string
s = FormatNumber(1234.5,2) 
nAmount = Nz(s, 0) ' OK
nAmount =  Val(s) ' NO - gives 1
        

Access Parameters

By default parameters are passed by reference, overide by using ByVal.

Runtime Version

Unhandled errors causes the program to shut down.

Starting application with a /runtime switch to test behaviour.

Saving Access Query to Excel

see code

ADODB does not work with linked spreadsheets.

Office 2007 Ribbon Documentation

http://msdn.microsoft.com/en-gb/library/dd756403.aspx

Access Autonumber Reset

In Access 2007 delete all data and then compact database autonumber fields are reset to start at 1.

Access File Extensions

.accdr creates a readonly version for full access to look like runtime environment - do this by simply renaming .accdb using Windows Explorer.

File Search in Access 2007 : Scripting.FileSystemObject

The FileSystemObject class has been removed from the Visual Basic for Applications object library for Access 2007. To use the FileSystemObject class, you must select Microsoft Scripting Run-time (Scrrun.dll) in the References dialog box for the project.

http://support.microsoft.com/kb/935402

Access Form Sizes in Inches/Centimetres

19" Monitor 1440 x 900
diagonal pixels = 1698
actual dpi = 90 dpi

Access uses the screen dpi to convert twips (1/1440 inch)

http://www.applecore99.com/api/api012.asp shows how to convert twips to pixels.

Microsoft Entity Framework ASP.Net

ASP.Net Entity Framework does not support Access databases.

Updating Excess Spreadsheets from Access VBA

ADODB does not support updates to linked spreadsheets.

to be expanded with example

Linking Access to FoxPro Database with ODBC

   
    Dim sql As String, DBPath as string
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    DBPath = "C:\PSS\FoxPro\BrodexWT\Data\BR.dbc"

    cn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
            "SourceType=DBC;" & _
            "SourceDB=" & DBPath & ";" & _
            "Exclusive=No"
    rs.Open ADODBSQL("SELECT * FROM Customer WHERE Name " & SQLStrContains(Me.customerName) & " ORDER BY Name"), cn
    
    Do While Not rs.EOF
        Debug.Print rs!CustID & ";" & rs!Name & ";" & rs!address
        rs.MoveNext
    Loop
    rs.Close

If you use a File DSN to import or link a FoxPro ODBC data source, you may receive the following error:
Reserved error (-7778); there is no message for this error. Solution: Use a Machine DSN (that is a User or System DSN) instead of a File DSN. http://support.microsoft.com/kb/212886

Download FoxPro ODBC driver at: http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

The recommended OLE DB driver is no use for Access.

Stuff you can and cannot do with the VFP ODBC driver

Access 2003 and 2007 on Same Machine

On a computer that has multiple versions of Access installed, the Windows Installer may start when you start Microsoft Office Access 2003 or Microsoft Office Access 2007. And, a "preparing to install" message or a "configuring" message may be displayed. You receive these messages before Access starts.

The Windows Installer repair operation registers Access 2003 every time that you start Access 2003 after you use Access 2007. Similarly, the Windows Installer repair operation registers Access 2007 every time that you start Access 2007 after you use an earlier version of Access. Apart from occasion use this not practical. Consider using Windows Virtual PC for the older version of Access.

Access 2000 and Access 2002 do not initiate a repair operation after you use a later version of Access. If the version of Access that you start is the same as the version of Access that you previously used, the Windows Installer repair operation does not occur.

Access Secuity and Linked Databases

One option is to hide the linked database using windows explorer.

The more secure option is to encrypt. Open backend db exclusive, encrypt, open front end delete all links and re-create - relinking does not work.

Very good article: http://msdn.microsoft.com/en-us/library/bb421308(v=office.12).aspx