Visual Basic Code Examples For Access

• Visual Basic Code Examples For Access Control
• Visual Basic Code Examples For Access Codes
• Microsoft Access Visual Basic

HTML Source EditorWord Wrap

This section of FreeVBCode provides free code on the topic of Database. These free Visual Basic code snippets, examples, and articles are available for you to download and review. Sample Code For Your Microsoft Access Database Winning Solutions, Inc. (WSI) has developed hundreds of VBA (Visual Basic for Applications) code functions throughout our history. Included below are a few links to a few sample functions. Note all code is in a class, this is the best way to work with data, keep user interface separate form data operations. In the form you create an instance of the class and call a method. Note the database in this case is in the same folder as the application's executable file e.g. In Visual Studio project the Bin Debug folder. When you’re automating an Access 2013 database application by using Visual Basic for Applications (VBA), you can get lost with a blank page to begin writing code. Where do you start? Here are a few simple guidelines to follow that will have you writing VBA code like a pro: Get help. Press F1 at any. Visual Basic Sample Codes Ebook. Visual Basic Sample Codes E-Book is written by our tutor, Dr.Liew. It comprises 258 pages of captivating contents and 48 fascinating Sample Codes.Perfect source of reference for your VB projects. Check it out by clicking the book picture.

In visual basic, Access Modifiers are the keywords and those are useful to define an accessibility level for all the types and type members.

By specifying the access level for all the types and type members, we can control whether they can be accessed in other classes or in current assembly or in other assemblies based on our requirements.

The following are the different types of access modifiers available in a visual basic programming language.

• Friend (internal in c#)

By using these four access modifiers, we can specify a following six levels of accessibility for all types and type members based on our requirements.

Generally, in visual basic only one access modifier is allowed to use with any member or type, except when we use Protected Friend combination.

In visual basic, we are not allowed to use any access modifiers on namespaces, because the namespaces have no access restrictions.

Only certain access modifiers are allowed to specify based on the context in which the member declaration occurs. In case, if we didn’t mention any access modifiers during the member declaration, then the default access modifiers will be used depending on the member declaration context.

For example, the top-level types which are not nested in any other types can only have Public or Friend accessibility. The default accessibility for top-level types is Friend.

Visual Basic Public Access Modifier

In visual basic, Public modifier is useful to specify that access is not restricted, so the defined type or member can be accessed by any other code in the current assembly or another assembly that references it.

Following is the example of defining members with Public modifier in a visual basic programming language.

Module Module1

ClassUser

Public Name AsString

Public Location AsString

Public Age AsInteger

PublicSub GetUserDetails()

Console.WriteLine('Name: {0}', Name)

Console.WriteLine('Location: {0}', Location)

Console.WriteLine('Age: {0}', Age)

EndSub

EndClass

Sub Main()

Dim u AsUser = NewUser()

u.Name = 'Suresh Dasari'

u.Location = 'Hyderabad'

u.Age = 32

u.GetUserDetails()

Console.WriteLine(vbLf & 'Press Enter Key to Exit..')

Console.ReadLine()

EndSub

EndModule

If you observe the above example, we defined a Userclass with required variables and method using Public access modifier and trying to access those variables and method in another class with an object reference of Userclass.

When we execute the above visual basic program, we will get the result as shown below.

Visual Basic Code Examples For Access Control

If you observe the above result, we are able to access the variables and methods of Userclass in another class because of specifying with Public specifier based on our requirements.

As discussed, the Public access specifier will make all the defined members or types available to all the types in our application.

Visual Basic Private Access Modifier

In visual basic, Private modifier is useful to specify that access is limited to the containing type so the defined type or member can only be accessed by the code in the same class or structure.

Following is the example of defining members with Private modifier in a visual basic programming language.

Module Module1

ClassUser

Private Name AsString

Private Location AsString

Private Age AsInteger

PrivateSub GetUserDetails()

Console.WriteLine('Name: {0}', Name)

Console.WriteLine('Location: {0}', Location)

Console.WriteLine('Age: {0}', Age)

EndSub

EndClass

Sub Main()

Dim u AsUser = NewUser()

' Complie-time Error

' These are inaccessible due to private specifier

u.Name = 'Suresh Dasari'

u.Location = 'Hyderabad'

u.Age = 32

u.GetUserDetails()

Console.WriteLine(vbLf & 'Press Enter Key to Exit..')

Console.ReadLine()

EndSub

EndModule

If you observe the above example, we defined a Userclass with required variables and method using Private access modifier and trying to access those variables and method in another class with an object reference of Userclass.

When we execute the above visual basic program, we will get compile-time errors like as shown below.

If you observe the above result, we got the compile-time error because the Private modifier members of Userclass referred in another class.

As discussed, the Private modifier type or member can be accessed only by the code within the same class or structure.

Visual Basic Protected Access Modifier

In visual basic, Protected modifier is useful to specify that access is limited to the containing type or types derived from the containing class so the type or member can only be accessed by code within the same class or in a derived class.

Following is the example of defining members with Protected modifier in a visual basic programming language.

Module Module1

ClassUser

Protected Name AsString

Protected Location AsString

Protected Age AsInteger

ProtectedSub GetUserDetails()

Console.WriteLine('Name: {0}', Name)

Console.WriteLine('Location: {0}', Location)

Console.WriteLine('Age: {0}', Age)

EndSub

EndClass

Sub Main()

Dim u AsUser = NewUser()

' Complie-time Error

' These are inaccessible due to protected specifier

u.Name = 'Suresh Dasari'

u.Location = 'Hyderabad'

u.Age = 32

u.GetUserDetails()

Console.WriteLine(vbLf & 'Press Enter Key to Exit..')

Console.ReadLine()

EndSub

EndModule

If you observe the above example, we defined a Userclass with required variables and method using Protected access modifier and trying to access those variables and method in another class with an object reference of Userclass.

When we execute the above visual basic program, we will get compile-time errors like as shown below.

If you observe the above result, we got a compile-time error because the Protected modifier members of Userclass referred in another class.

As discussed, the Protected members of a base class can be accessible in a derived class, only when access occurs through the derived class type.

Following is the example of accessing a base classProtected members in derived class through derived class type.

Class User

Protected Name AsString

Protected Location AsString

Protected Age AsInteger

ProtectedSub GetUserDetails()

Console.WriteLine('Name: {0}', Name)

Console.WriteLine('Location: {0}', Location)

Visual Basic Code Examples For Access Codes

Console.WriteLine('Age: {0}', Age)

EndSub

EndClass

Class Details

InheritsUser

PublicSharedSub Main()

Dim u AsUser = NewUser()

Dim d AsDetails = NewDetails()

' Complier Error

' protected members can only accessible with derived classes

Microsoft Access Visual Basic

' u.Name = 'Suresh Dasari';

d.Name = 'Suresh Dasari'

d.Location = 'Hyderabad'

d.Age = 32

d.GetUserDetails()

Console.WriteLine(vbLf & 'Press Enter Key to Exit..')

Console.ReadLine()

EndSub

EndClass

If you observe the above example, we are accessing base class (User) Protected members using the reference of derived class (Details) and if we uncomment the commented code we will get a compile-time error because we are trying to access the protected members with base class (User) reference instead of derived class (Details).

When we execute the above visual basic program, we will get the result as shown below.

This is how we can use Protected modifier in our visual basic applications to limit access of type or member in the same class or derived class based on our requirements.

Visual Basic Friend Access Modifier

In visual basic, Friend modifier is useful to specify that access is limited to the current assembly so the type or member can be accessed by any code in the same assembly, but not from another assembly.

Following is the example of defining members with Friend modifier in a visual basic programming language.

Module Module1

ClassUser

Friend Name AsString

Friend Location AsString

Friend Age AsInteger

FriendSub GetUserDetails()

Console.WriteLine('Name: {0}', Name)

Console.WriteLine('Location: {0}', Location)

Console.WriteLine('Age: {0}', Age)

EndSub

EndClass

Sub Main()

Dim u AsUser = NewUser()

u.Name = 'Suresh Dasari'

u.Name = 'Suresh Dasari'

u.Location = 'Hyderabad'

u.Age = 32

u.GetUserDetails()

Console.WriteLine(vbLf & 'Press Enter Key to Exit..')

Console.ReadLine()

EndSub

EndModule

If you observe the above example, we defined a Userclass with required variables and method using Friend access modifier and trying to access those variables and method in another class with an object reference of Userclass.

When we execute the above visual basic program, we will get the result as shown below.

If you observe the above result, we are able to access the variables and methods of Userclass in another class because of specifying with Friend specifier based on our requirements.

As discussed, in visual basic the Friend type or members are accessible within the files of the same assembly.

Visual Basic Protected Friend Access Modifier

In visual basic, the Protected Friend modifier is useful to specify that access is limited to the current assembly or types derived from the containing class. So, the type or member can be accessed by any code in the same assembly or by any derived class in another assembly.

Following is the example of defining members with Protected Friend modifier in a visual basic programming language.

Module Module1

ClassUser

ProtectedFriend Name AsString

ProtectedFriend Location AsString

ProtectedFriend Age AsInteger

ProtectedFriendSub GetUserDetails()

Console.WriteLine('Name: {0}', Name)

Console.WriteLine('Location: {0}', Location)

Console.WriteLine('Age: {0}', Age)

EndSub

EndClass

Sub Main()

Dim u AsUser = NewUser()

u.Name = 'Suresh Dasari'

u.Name = 'Suresh Dasari'

u.Location = 'Hyderabad'

u.Age = 32

u.GetUserDetails()

Console.WriteLine('Press Enter Key to Exit..')

Console.ReadLine()

EndSub

EndModule

If you observe the above example, we defined a Userclass with required variables and method using Protected Friend access modifier and trying to access those variables and method in another class with an object reference of Userclass.

When we execute the above visual basic program, we will get the result as shown below.

If you observe the above result, we are able to access the variables and methods of Userclass in another class because of specifying with Friend specifier based on our requirements.

As discussed, in visual basic the Protected Friend type or members are accessible from the current assembly or from the types that are derived from the containing class in another assembly.

The Microsoft Access RunSQL method performs the RunSQL action in Visual Basic. This command is used to execute sql query code within Access Visual Basic. Typically you would want to use the docmd.runsql for Update Queries and Delete Queries. If you want to work with the data in VBA then you would do a Select Query using a Recordset operation.

SQL Statement – A required variant string expression that is a valid SQL statement for an action query or a>MS Access RunSQL Example:

Public Sub RUN_Query

Dim SQL_Text as String

SQL_Text = “Delete * from M_Employees”

Docmd.RunSQL (SQL_Text, false)

End Sub

The RunSQL command is one of the most powerful features of Access Visual Basic. The programmer has the ability to perform all the logic and data validation functions within VBA and has the power of SQL all within the same environment.

New! Download a running example of the Docmd.RunSQL Method

Such functions as creating temporary tables and building queries based on the user’s responses to question creates an environment of almost infinite flexibility.

A tip for creating your own RunSQL code is the create your query in the Access Query Design tool and then view the SQL view and copy and paste the SQL code into your Visual Basice window… this will give you a great starting point.

Below is an example of appending data to a temporary table based on the records within a master form on the screen. We use a local variable (SQLText) to hold the SQL text – this makes debugging easier because you can use a MsgBox to display the value of SQLText and you can also place the SQLText value into a little text field on the form and that way you can copy it and paste it in the Access Query Design tool to help debug problems. This example gives us programmatic control of the details form data. Note that the records for the detail form comes from separate tables. This query could also have been done using aUnion query:

Private Sub Form_Current()
Dim SQLText As String

‘ JW Dean Blue Claw Database Design

‘ disable editing if previous order number has been filledin
‘
If IsNull(Me.Previous_Order_) = True Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

‘ clear out temp table
DoCmd.RunSQL “Delete * from t_orders”
‘
‘create and run the append queries
‘
SQLText = “INSERT INTO T_Orders ( Order_Numb, ITEMDESC, XTNDPRCE, QUANTITY ) SELECT SOPNUMBE, ITEMDESC, XTNDPRCE, QUANTITY ” & _
“FROM SOP10200 where SOPNumbe='” & Me.Previous_Order_ & “‘ or sopnumbe='” & Me.ReplOrder_ & “‘ or sopnumbe='” & Me.CR_ & “‘”
‘
DoCmd.RunSQL SQLText
‘
‘
SQLText = “INSERT INTO T_Orders ( Order_Numb, ITEMDESC, XTNDPRCE, QUANTITY ) SELECT SOPNUMBE, ITEMDESC, XTNDPRCE, QUANTITY ” & _
“FROM SOP30300 where SOPNumbe='” & Me.Previous_Order_ & “‘ or sopnumbe='” & Me.ReplOrder_ & “‘ or sopnumbe='” & Me.CR_ & “‘”
‘
DoCmd.RunSQL SQLText
‘ refresh the form
Form_F_Previous_Details.Requery
End Sub

More runsql examples:

Docmd
DoCmd Run SQL, Run an SQL action query by using the SQL command. You may also run a DDL query. DoCmd Send Object, Include the specified datasheet, form,…

Inventory Calculations Example – Single User Databases
This Access database download shows how you can use the Docmd.Runsql statement in the after update (afterupdate) event to increment and decrement inventory …

Multi-Select Parameter Forms
select name,address1,address2,city,state,zip from q_daily_prospect_Pitney” DoCmd.RunSQL (sqltext) Set rst = db.OpenRecordset(“select count(name) as icount …

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016