====== PtrSafe Access adjustment for 64 bit ======
2020-02-26
Compile error 64-bit
Change this
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
to this and it will compile on both 32-bit and 64-bit
#If Win64 Then
Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#Else
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If
From
2020-02-20
Compile error
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
hHandle As Long, ByVal dwMilliseconds As Long) As Long
====== Can t find project or library ======
dtFMSProcessingDate = Date
MsgBox VBA.Date
From
Also
2021-10-09
TRIM() not found? Compile error: Can't find project or library in Excel 2007
From
Answer: **On the VBA window go to Tools->References and uncheck the MISSING references.**
ClosedXML - Create Excel files in .Net
• Proposed as answer byTim Johnson at AptosFriday, June 6, 2014 5:13 PM
• Unproposed as answer byTim Johnson at AptosFriday, June 6, 2014 5:13 PM
Thursday, January 20, 2011 4:54 AM
From
====== How to display Access query results without having to create temporary query? ======
From: http://stackoverflow.com/questions/17328092/how-to-display-access-query-results-without-having-to-create-temporary-query
Const cstrQueryName As String = "TemporaryQuery"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sqlStr As String
'*Logic to create SQL query... variable to hold query is called sqlStr*
' Apparently you have that piece worked out. I'll use a simple query ...
sqlStr = "SELECT * FROM Dual;"
Set db = CurrentDb
If Not QueryExists(cstrQueryName) Then
Set qdf = db.CreateQueryDef(cstrQueryName)
Else
Set qdf = db.QueryDefs(cstrQueryName)
End If
qdf.sql = sqlStr
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery cstrQueryName
If you still want to later discard the saved query, do this ...
If QueryExists(cstrQueryName) Then
DoCmd.DeleteObject acQuery, cstrQueryName
End If
This is the helper function for the above code ...
Public Function QueryExists(ByVal pName As String) As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim blnReturn As Boolean
Dim strMsg As String
On Error GoTo ErrorHandler
blnReturn = False ' make it explicit
Set db = CurrentDb
Set qdf = db.QueryDefs(pName)
blnReturn = True
ExitHere:
Set qdf = Nothing
Set db = Nothing
QueryExists = blnReturn
Exit Function
ErrorHandler:
Select Case Err.Number
Case 3265 ' Item not found in this collection.
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure QueryExists"
MsgBox strMsg
End Select
GoTo ExitHere
End Function
====== SaveAsText() in Access to export all code ======
From: http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development
script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules.
Updated to use OpenAccessProject() if it sees a .adp extension, else use OpenCurrentDatabase()
----------------------------------------------------------------------------------------------
' Usage:
' CScript decompose.vbs
' Converts all modules, classes, forms and macros from an Access Project file (.adp) to
' text and saves the results in separate files to . Requires Microsoft Access.
'
Option Explicit
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
dim sADPFilename
If (WScript.Arguments.Count = 0) then
MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))
Dim sExportpath
If (WScript.Arguments.Count = 1) then
sExportpath = ""
else
sExportpath = WScript.Arguments(1)
End If
exportModulesTxt sADPFilename, sExportpath
If (Err <> 0) and (Err.Description <> NULL) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function exportModulesTxt(sADPFilename, sExportpath)
Dim myComponent
Dim sModuleType
Dim sTempname
Dim sOutstring
dim myType, myName, myPath, sStubADPFilename
myType = fso.GetExtensionName(sADPFilename)
myName = fso.GetBaseName(sADPFilename)
myPath = fso.GetParentFolderName(sADPFilename)
If (sExportpath = "") then
sExportpath = myPath & "\Source\"
End If
sStubADPFilename = sExportpath & myName & "_stub." & myType
WScript.Echo "copy stub to " & sStubADPFilename & "..."
On Error Resume Next
fso.CreateFolder(sExportpath)
On Error Goto 0
fso.CopyFile sADPFilename, sStubADPFilename
WScript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
WScript.Echo "opening " & sStubADPFilename & " ..."
If (Right(sStubADPFilename,4) = ".adp") Then
oApplication.OpenAccessProject sStubADPFilename
Else
oApplication.OpenCurrentDatabase sStubADPFilename
End If
oApplication.Visible = false
dim dctDelete
Set dctDelete = CreateObject("Scripting.Dictionary")
WScript.Echo "exporting..."
Dim myObj
For Each myObj In oApplication.CurrentProject.AllForms
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "\" & myObj.fullname & ".form"
oApplication.DoCmd.Close acForm, myObj.fullname
dctDelete.Add "FO" & myObj.fullname, acForm
Next
For Each myObj In oApplication.CurrentProject.AllModules
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "\" & myObj.fullname & ".bas"
dctDelete.Add "MO" & myObj.fullname, acModule
Next
For Each myObj In oApplication.CurrentProject.AllMacros
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "\" & myObj.fullname & ".mac"
dctDelete.Add "MA" & myObj.fullname, acMacro
Next
For Each myObj In oApplication.CurrentProject.AllReports
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "\" & myObj.fullname & ".report"
dctDelete.Add "RE" & myObj.fullname, acReport
Next
WScript.Echo "deleting..."
dim sObjectname
For Each sObjectname In dctDelete
WScript.Echo " " & Mid(sObjectname, 3)
oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
Next
oApplication.CloseCurrentDatabase
oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
oApplication.Quit
fso.CopyFile sStubADPFilename & "_", sStubADPFilename
fso.DeleteFile sStubADPFilename & "_"
End Function
Public Function getErr()
Dim strError
strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
"From " & Err.source & ":" & vbCrLf & _
" Description: " & Err.Description & vbCrLf & _
" Code: " & Err.Number & vbCrLf
getErr = strError
End Function
====== Replacing the Calendar Control in Access 2010 Applications ======
Calendar control
From: http://msdn.microsoft.com/en-us/library/office/gg251104%28v=office.14%29.aspx
Microsoft Access has included a Calendar control since it first added support for ActiveX
controls, in Access 2.0. The calendar control, MSCAL.OCX, is not included with Access 2010.
Consider the following pros and cons of each option:
Built-in date picker
Simple to use (requires only setting the ShowDatePicker property).
No customization available.
Works only with TextBox controls bound to a Date field in a table.
DatePicker Control
Reasonably easy to use.
Allows more customization than the built-in date picker.
Can also be used to select times.
Can be bound to a data source.
Depends on the Microsoft Custom Controls DLL, which introduces serious versioning issues. Your
end-users may not have the same version of the DLL installed as you do, and this requires
updating their DLL to the latest version.
Only appears as a drop-down, and cannot appear as an open calendar.
Calendar Form
Appears only as an open calendar (both a pro and a con).
Completely customizable, but requires some work in the designer and in code.
Easy to use─merely drag onto a form and write the code.
Hooking up events requires additional code.
Binding is not available without writing code.
====== Alternatives to SendKey ======
From: http://www.pcreview.co.uk/forums/alternative-sendkeys-keystrokes-f9-wait-yes-t3162974.html
DoCmd.GoToControl "Balance"
From: http://www.access-programmers.co.uk/forums/showthread.php?t=47212
By the way, in that code snippet, you might be wondering why moving the focus to the txtField field
involves two steps:
1- Me.subForm.SetFocus
and then,
2- Me.subForm.Form.Controls("txtField").SetFocus
====== num lock keeps shutting off ======
From: http://answers.microsoft.com/en-us/windows/forum/windows_7-performance/my-num-lock-keeps-shutting-itself-off-by-itself/f50053ac-826b-4a35-acc7-16fe7c431f77
From: http://support.microsoft.com/kb/179987
Multiple Sendkeys
From: http://bytes.com/topic/access/answers/943432-sendkeys-turns-off-numlock
SendKeys statement activates, the NumLock status automatically turns OFF
====== How to solve missing MSCAL.OCX reference in Microsoft Access 2010 ======
From: http://community.spiceworks.com/how_to/show/5517-how-to-solve-missing-mscal-ocx-reference-in-microsoft-access-2010
From: https://social.msdn.microsoft.com/Forums/office/en-US/fd1d449e-d39c-4c0b-99ea-ed561d8906ce/mscalocx-microsoft-calendar-control-conversion-to-mscomct2ocx?forum=exceldev
However note, that the solution specified is a work-around for the above problem. Microsoft has stated
that the Calendar control feature is deprecated in Access 2010 and has suggested a number of options
for replacing it. See the link referenced at the bottom of this page for more details
Centerport
I. Click on the Windows Start menu and select 'Run'
II. In the Run command window enter:
regsvr32 %SystemRoot%\syswow64\mscal.ocx
III. Select OK to execute the command
====== Forms: Date Picker without ActiveX ======
From: http://access.mvps.org/access/forms/frm0057.htm
Forms: Date Picker without ActiveX; I have a form with a date field or a block of code that needs to
input a date and I want to the user to have a calendar view to select
that date from. I don't want to use any ActiveX components.
(A) Use the Access Date Picker.
====== wildcards for SQL ======
From: http://msdn.microsoft.com/en-us/library/office/aa140104(v=office.10).aspx
The pattern-matching characters we looked at last month were provided through DAO. Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character. There's no single-digit wildcard available through ADO that equates to DAO's pound sign wildcard (#); however, character lists and ranges behave the same.
====== split for openargs ======
http://movefirstblog.wordpress.com/2008/07/09/microsoft-access-parse-openargs-seperating-each-openarg-value/
====== visual-basic-combo-box-tutorial ======
[[http://www.vb6.us/tutorials/visual-basic-combo-box-tutorial]]
[[http://www.mvps.org/access/api/api0002.htm]]
For an action query you might do something like:
Dim strSQL As String
strSQL = "Delete * From MyTable Where ID =" & Me.txtID
CurrentDB.Execute strSQL
You can also use DoCmd.RunSQL:
DoCmd SetWarning False
DoCmd.RunSQL strSQL
DoCmd SetWarning True
Or you might build a recordset:
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
strSQL = "Select * From MyTable"
Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
[[http://www.databasejournal.com/features/msaccess/article.php/3505836/Executing-SQL-Statements-in-VBA-Code.htm]]