Visual Basic ADO to Page Through Records 10 at a Time

0 comments

Posted on 7th July 2011 by Admin in Visual Basic .Net

, ,

Option Explicit

Private m_CombinedNames As String
‘Display the first 10 records.

Private Sub cmdList_Click()
 ‘ Reset m_CombinedNames
 ‘ to select the first record.
 m_CombinedNames = “,”
 ‘ Get the next 10 records.
 cmdNext.Enabled = True
 cmdNext_Click

End Sub

‘ Display the next 10 records.

Private Sub cmdNext_Click()
 Dim DB_Name As String
 Dim Conn As ADODB.Connection
 Dim RS As ADODB.Recordset
 Dim txt As String
 Dim i As Integer
 ‘ Get the database name.

DB_Name = App.Path


If Right$(DB_Name, 1) <> “” Then DB_Name = DB_Name & “”
 DB_Name = DB_Name & “Employee.mdb”
 ‘ Open a connection.
 Set Conn = New ADODB.Connection
 Conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & DB_Name & “;” & _
 “Persist Security Info=False”
 Conn.Open
 ‘ Get the next 10 records.

Set RS = Conn.Execute(“SELECT TOP 10 * FROM Employees “ & “WHERE LastName + ‘,’ + _
 FirstName > “ & “‘” & m_CombinedNames & “‘ ORDER BY LastName, “ & “FirstName”)
 ‘ Display the records.
 Do until RS.EOF
 i = i + 1
 txt = txt & vbCrLf & Format$(RS!EmployeeId, “@@@”) & ” “ & _
 Format$(RS!LastName, “!@@@@@@@@@@@@@”) & Format$(RS!FirstName, “!@@@@@@@@@@@@@”)
 m_CombinedNames = RS!LastName & “,” & RS!FirstName
 RS.MoveNext
 Loop
 ‘ See if we ran out of records.
 If i < 10 Then
 txt = txt & vbCrLf & “<END>”
 cmdNext.Enabled = False
 End If
 ‘ Display the data.
 If Len(txt) > 0 Then txt = Mid$(txt, 3)
 txtEmployees.Text = txt

End Sub

Construct the Connection String at Runtime

0 comments

Posted on 5th July 2011 by Admin in Visual Basic .Net

, ,

‘How to Prompt user to construct the connection string at runtime!!!

‘It uses the same interface that ADODC, DataEnvironment etc use.

‘References:
’1. Microsoft OLE DB Service Component 1.0
’2. Microsoft ADO 2.5

‘Visual Basic Code Below


Public Function GetConnectionString(Optional strOLE As String = “OLEDB”) As String
On Error GoTo ErrHandler

Dim Conn As ADODB.Connection
Dim ObjectDataLink As MSDASC.DataLinks
Set Conn = New ADODB.Connection
Set ObjectDataLink = New MSDASC.DataLinks
Select Case strOLE
 Case “OLEDB”
 Conn = ObjectDataLink.PromptNew
 GetConnectionString = Conn.ConnectionString
 Case “ODBC”
 Conn.ConnectionString = “”
 Conn.Properties(“Prompt”) = adPromptAlways
 Conn.Open
 GetConnectionString = Conn.ConnectionString
 Conn.Close
End Select
Exit Function
ErrHandler:
 If Err.Number = 91 Or Err.Number = -2147217842 Then
 Exit Function
 Else
 MsgBox “Error: “ & Err.Description
 End If

End Function

How to Import SQL Server data into an MS Access Database

0 comments

Posted on 1st July 2011 by Admin in Database Programming |Visual Basic 6

, ,

‘Import SQL Server data into an Access database
‘You can quickly import SQL Server data into an Access database using an
‘Execute statement in either ADO or DAO

Private Sub Import_Data()

Dim Conn As ADODB.Connection
 Dim SQL_STR As String
 ‘Create ADO Connection Object
 Set Conn = New ADODB.Connection
 Conn.Open “Driver={Microsoft Access Driver (*.mdb)};” & “Dbq=ExportedData.mdb;” & _
 “DefaultDir=d:Temp;” & “Uid=Admin;Pwd=;” SQL_STR = “SELECT * INTO “ & “[table_data] “ & “FROM “ & “[ODBC;Driver=SQL Server; " & _
 "SERVER=ServerName;DATABASE=MyTable;" & "UID=MyUser;PWD=MyPassword;].” & _
 “[table_data];”
 Conn.Execute SQL_STR
Set Conn = Nothing

End Sub

‘Note : Please change the following :

‘ServerName = Your SQL Server Name or Address
‘UID = Your username to access the database
‘PWD = Your database password

‘you must create a temporary folder in drive c:, please see sample below
‘c:tempExportedData.mdb

How to open a password protected MS Access Database using ADO Object

0 comments

Posted on 28th March 2011 by Admin in Visual Basic .Net

,

‘Sample visual basic code on how to open a password protected access database using ADO object

Option Explicit

Dim Conn As New ADODB.Connection, ConStr As
String

Private Sub Btn_OpenDB_Method1()
‘Open the connection

Conn.Open “Provider=Microsoft Jet 4.0 OLE DB Provider;Data Source=” & App.Path & “Data1.mdb” & _

“;Jet ” & “OLEDB:Database Password=123456″


MsgBox “Data File is now opened”

Conn.Close

End Sub

Private Sub Btn_OpenDB_Method2()

ConStr = “Data Source=” & App.Path & “Data1.mdb”

Conn.Provider = “Microsoft Jet 4.0 OLE DB Provider”

Conn.ConnectionString = ConStr

Conn.Properties(“Jet OLEDB:Database Password”) = “123456″

Conn.Open


MsgBox “Data File is now opened”

Conn.Close

End Sub

Sample DMO, RDO, DAO and ADO Demonstration

0 comments

Posted on 1st May 2010 by Admin in Database Programming |Visual Basic 6

, , , , ,

The program demonstrates major VB capabilities of database accessing. It demonstrates usage of DMO, RDO, ADO and DAO data access to local database and SQL database, too. It also shows a few abilities that are extremely suitable for usage in DB application, but they are not entirely documented in the documentations dedicated to VB databases programming as well as in the MSDN.
To enable all features of this demo, you must copy the provided access database to a SQL Server database and create a DSN for the latter. Follow the instructions in the readme.htm file.

Download Project

Download File