This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.
The following method exports all available queries in an Access database into individual text files, which are called like the corresponding query.
DAO does not provide the support ADODB gives you, therefore this method will only work with queries created and stored in a current MS Access database.
The method writeLineToTextFile is not a standard VBA method, but can be found at following URL.
The target encoding should not be UTF-16LE but ASCII, if you intend to use a versioning tool like GIT to keep track of changes in MS Access queries.
Write a String into a text file
Replacing special characters can be a pain, if you do not rely on regular expressions.
Replace special escape characters in String
Delete a file on a users hard disc (VBScript, which can easily be adjusted to VBA).
- Hits: 10654
Recently I had to link tables from one access file in another access file.
I did not intend to document such a trivial task on my webpage, but since the function I copied from the internet ran into an endless loop after I only provided an invalid file path I decided to implement something myself.
' Alexander Bolte
' Linking given table from given source Access database in CurrentDb under provided target name.
' If a linked table already exists under given name, the link will be broken and replaced with the new one.
' @Param strDatabaseSource
' String providing the full path of the source access database.
' @Param strTableSource
' String providing the source table name in the source access database.
' @Param strTableDestination
' String providing the target table name in CurrentDB.
' True, if linkage succeeded, else false.
Public Function LinkTable( _
ByVal strDatabaseSource As String, _
ByVal strTableSource As String, ByVal strTableDestination As String _
) As Boolean
Dim dbSource As DAO.Database
Dim dbTarget As DAO.Database
Dim dbDestination As DAO.Database
Dim tdf As DAO.TableDef
On Error GoTo LinkTable_Err
Set dbSource = DBEngine.Workspaces(0).OpenDatabase(strDatabaseSource)
Set dbDestination = CurrentDb
Set tdf = dbDestination.CreateTableDef(strTableDestination)
tdf.Connect = ";DATABASE=" & strDatabaseSource
tdf.SourceTableName = strTableSource
LinkTable = True
LinkTable_Err: If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, cDialogTitle
LinkTable = False
If Not (dbSource Is Nothing) Then
Set dbSource = Nothing
Set dbDestination = Nothing
Set tdf = Nothing
- Hits: 9101
If you have to change the sort order of data returned in a RecordSet to behave case sensitive in VBA you can use a trick, which is provided by Microsoft.
The below function returns a hexadecimal representation of a handed String, which can then be used in an ORDER BY clause of a sequel statement.
Original Source is available at following URL.
- Hits: 9386
Access VBA DAO Article Count: 2
Page 1 of 3