Reset the Seed of the AutoNumber using ADOX in VBAFunction ResetSeed(strTable As String) As String 'Purpose: Reset the Seed of the AutoNumber, using ADOX. Dim strAutoNum As String 'Name of...
Breaking News
Tuesday, June 26, 2012
Ex20: VBA - Read Seed / Autonumber using ADOX
Read the Seed of the AutoNumber of a table using ADOX in VBAFunction GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long 'Purpose: Read the Seed of the AutoNumber of a table....
Ex19: VBA - Delete all record using ADOX
Delete all records from the table, and reset the AutoNumber using ADOX in VBAFunction DeleteAllAndResetAutoNum(strTable As String) As Boolean 'Purpose: Delete all records from the table, and reset...
Ex18: Create a database using ADOX
Function CreateDatabaseAdox() 'Purpose: Create a database using ADOX. Dim cat As New ADOX.Catalog Dim strFile As String strFile = "C:\SampleADOX.mdb" cat.Create "Provider='Microsoft.Jet.OLEDB.4.0';"...
Ex17: VBA - Delete a parameter/action query using ADOX
Function DeleteProcedureAdox() 'Purpose: Delete a parameter/action query using ADOX. Dim cat As New ADOX.Catalog Dim cmd As ADODB.Command Dim lngCount As Long 'Initialize. cat.ActiveConnection...
Ex16: VBA - Execute a parameter query using ADOX
Function ExecuteProcedureAdox() 'Purpose: Execute a parameter query using ADOX. Dim cat As New ADOX.Catalog Dim cmd As ADODB.Command Dim lngCount As Long 'Initialize. cat.ActiveConnection...
Ex14: VBA - Create a parameter / action query using ADOX
Function CreateProcedureAdox() 'Purpose: Create a parameter query or action query using ADOX. Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim strSql As String 'Initialize....
Ex15: VBA - List the parameter/action queries using ADOX
Function ShowProx() 'Purpose: List the parameter/action queries using ADOX. Dim cat As New ADOX.Catalog Dim proc As ADOX.Procedure Dim vw As ADOX.View cat.ActiveConnection = CurrentProject.Connection...
Ex13: VBA - Create a query using ADOX
Function CreateViewAdox() 'Purpose: Create a query using ADOX. Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim strSql As String 'Initialize. cat.ActiveConnection...
Ex12: VBA - Delete relationships using ADOX
Function DeleteKeyAdox() 'Purpose: Delete relationships using ADOX. Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table Set cat.ActiveConnection = CurrentProject.Connection cat.Tables("tblAdoxBooking").Keys.Delete...
Ex11: VBA - List relationships using ADOX
Function ShowKeyAdox(strTableName As String) 'Purpose: List relationships using ADOX. Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table Dim ky As ADOX.Key Dim strRIName As String ...
Ex10: VBA - Create relationships using ADOX
Show how to create relationships using ADOX in VBAFunction CreateKeyAdox() 'Purpose: Show how to create relationships using ADOX. Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table Dim...
Ex9: VBA - Delete indexes using ADOX
Show how to delete indexes using ADOX in VBAFunction DeleteIndexAdox() 'Purpose: Show how to delete indexes using ADOX. Dim cat As New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection...
Ex8: VBA - Create indexes using ADOX
Show how to create indexes using ADOX in VBAFunction CreateIndexesAdox() 'Purpose: Show how to create indexes using ADOX. Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table Dim ind As ADOX.Index...
Ex7: VBA - Delete a table using ADOX
Function DeleteTableAdox() 'Purpose: Delete a table using ADOX. Dim cat As New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection cat.Tables.Delete "MyTable" Set cat...
Ex6: VBA - Modify field properties using ADOX
Show how to alter field properties using ADOX in VBAFunction ModifyFieldPropAdox() 'Purpose: Show how to alter field properties, using ADOX. 'Note: You cannot alter the DefinedSize of the...
Ex5: VBA - Modify table using ADOX
Show how to add fields to a table, and delete them using ADOX in VBAFunction ModifyTableAdox() 'Purpose: Show how to add fields to a table, and delete them using ADOX. Dim cat As New ADOX.Catalog...
Ex4: VBA - Create a table using ADOX
Create a table with various field types using ADOX in VBAFunction CreateTableAdox() 'Purpose: Create a table with various field types, using ADOX. Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table...
Ex3: VBA - Show the columns in a table using ADOX
Show the columns in a table, and optionally their propertiesFunction ShowPropsADOX(strTable As String, Optional bShowPropertiesToo As Boolean) 'Purpose: Show the columns in a table, and optionally...
Ex2: VBA - List the tables using ADOX
Function ShowAllTables(Optional bShowFieldsToo As Boolean) 'Purpose: List the tables (and optionally their fields) using ADOX. Dim cat As New ADOX.Catalog 'Root object of ADOX. Dim tbl As...
Ex1: VBA - Set the Seed of an AutoNumber using ADOX
Function SetSeed(strTable As String, strAutoNum As String, lngID As Long) As Boolean 'Purpose: Set the Seed of an AutoNumber using ADOX. Dim cat As New ADOX.Catalog Set cat.ActiveConnection...
ADOX Programming Code Examples in VBA
This page is a reference for developers, demonstrating how to use the ADOX library to programmatically create, delete, modify, and list the objects in Access - the tables, fields, indexes, and relations,...
Monday, June 25, 2012
Ex8: VBA - Distinct users Connected to DB using ADO
Count the number of distinct users connected to the database using ADO in VBAFunction UserCount() As Long Dim cnLocal As ADODB.Connection 'Current project connection. Dim cnBackEnd...
Ex7: VBA - Database Active users in ADO
List the users currently connected to the database using ADO in VBAFunction ShowUserRosterMultipleUsers() 'Source: kb 198755. Dim cn As New ADODB.Connection 'Dim cn2 As New ADODB.Connection...
Ex6: VBA - BNOT Operator in ADO
Illustrate BNOT (binary NOT) operator (ADO only.) in VBAFunction TestBnot() 'Purpose: Illustrate BNOT (binary NOT) operator (ADO only.) Dim cn As ADODB.Connection Dim strSql As String ...
Ex5: VBA - BAND Operator in ADO
Illustrate the BAND operator with literals. (ADO only.) in VBAFunction ShowBand() Dim rs As New ADODB.Recordset rs.Open "SELECT (2 BAND 4) AS Result;", CurrentProject.Connection ShowBand =...
Ex4: VBA - Modify a query using ADO
Modify a query using ADO in VBAFunction ModifyViewAdo() 'Purpose: Modify a query using ADO. Dim cn As ADODB.Connection Dim strSql As String strSql = "ALTER TABLE Query1 AS SELECT MyTable.*...
Ex3: VBA - Create a new query using ADO
Create a new query using ADO in VBAFunction CreateViewAdo() 'Purpose: Create a new query using ADO. Dim cn As ADODB.Connection Dim strSql As String strSql = "CREATE VIEW MyTableView...
Ex2: VBA - Open a recordset using ADO
Open a recordset using ADO in VBAFunction AdoRecordsetExample() 'Purpose: Open a recordset using ADO. Dim rs As New ADODB.Recordset Dim strSql As String strSql = "SELECT MyField FROM...
Ex1: VBA - List the tables using ADO
List the tables using ADO in VBAFunction ShowSchema() 'Purpose: List the tables, using ADO. Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim i As Integer Set cn = CurrentProject.Connection...
ADO programming examples in VBA
This page is a reference for developers, demonstrating how to use the ADO library to list and manipulate the objects in Access.ADO (ActiveX Data Objects) is more generic than DAO (the one...
Sunday, June 24, 2012
Ex24: VBA - GetName of AutoNumber using DAO
Get the name of the AutoNumber field, using DAO.Option Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const intcIndexGeneral...
Ex23: VBA - Execute SQL using DAO
Execute the SQL statement on the current database in a transaction using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As...
Ex22: VBA - Show form properties using DAO
Loop through the controls on a form, showing names and properties using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As...
Ex21: VBA - Open & Loop through records using DAO
How to open a recordset and loop through the records using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private...
Ex20: VBA - Convert numeric results using DAO
Converts the numeric results of DAO fieldtype to text using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private...
Ex19: VBA - Read table records using DAO
How to read the field names and types from a table or query.Option Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const...
Ex18: VBA - Read fields using DAO
How to read the fields of a table using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const intcIndexGeneral...
Ex17: VBA - Show DB properties using DAO
List the properties of the current database using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const...
Ex16: VBA - Create new database using DAO
Create a new database programmatically, and set its key properties using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As...
Ex15: VBA - Create a query using DAO
Create a query programmatically using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const intcIndexGeneral...
Ex14: VBA - Index on field using DAO
Indicate if there is a single-field index using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const...
Ex13: VBA - Set field description using DAO
Assign a Description to a field using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private Const intcIndexGeneral...
Ex12: VBA - Proper() function using DAO
Convert mixed case name into a name with spaces using DAO in VBAOption Compare DatabaseOption Explicit'Constants for examining how a field is indexed.Private Const intcIndexNone As Integer = 0Private...
Subscribe to:
Posts (Atom)