Windows Forms - Módulo de acesso ao MS ACCESS
Imports System.Data.OleDb 'acesso ao access
''' <summary>
''' Para acessar os objetos ADODB precisa Projeto, Adicionar Referencia, Assemblies, Extensions, ADODB
''' </summary>
Module Access
Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=pasta\arquivo.mdb"
Preenche uma DropDownList com o conteúdo de uma pesquisa do MS ACCESS
Public Function PreecheDDLComboBox() As Boolean
Dim sql As String
Dim cmd As OleDbCommand
Dim conn As New OleDbConnection(constr)
Dim a As String 'text do dropDownList
Dim b As String 'value do dropDownList
Dim cbb As New ComboBox 'no aspx é dropdownlist
'lblmsg.Text = ""
'If Not IsPostBack Then
sql = "SELECT T1,N1 FROM COLABORADORES" 't1=nome, n1=FIXO-RM-ID
cmd = New OleDbCommand(sql, conn)
Try
'colaboradores
cbb.Items.Clear()
'ddlColaboradores.Items.Add(New ListItem("", ""))
cbb.Items.Add("")
conn.Open()
Dim read As OleDbDataReader = cmd.ExecuteReader()
If read.HasRows Then
While read.Read()
a = read.Item("T1").ToString
b = read.Item("N1").ToString
'ddlColaboradores.Items.Add(New ListItem(a, b))
cbb.Items.Add(a + vbTab + b)
End While
End If
read.Close()
Return True
Catch ex As Exception
'lblmsg.Text = ex.Message
Return False
Finally
conn.Close()
End Try
End Function
Executa uma ou mais queries no MS ACCESS
''' <summary>
''' Executa uma ou mais queries
''' As queries podem ser agrupadas em um único string separadas por CRLF
''' </summary>
''' <param name="Querie"></param>
''' <returns></returns>
'''
Public Function ExecutarQuerie(Querie As String) As Boolean 'btnExecutar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExecutar.Click
Dim Comando As New OleDbCommand
Dim ConexaoBD As New OleDbConnection
Dim SenhaBD As String = ""
Dim Sql As String
'Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Colegio Santa Isabel\BD Site1 2014\" + lstBD.Text + ".mdb"
Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=e:\pastalstBD.mdb"
Dim a As Integer
Dim queries() As String
'btnExecutar.Enabled = False
queries = Split(Querie, vbCrLf)
ConexaoBD = New OleDbConnection(constr)
ConexaoBD.Open()
Try
For a = 0 To UBound(queries)
'txtandamento.Text = CStr(a)
Sql = queries(a)
If Sql <> "" Then
Comando = New OleDbCommand(Sql, ConexaoBD) 'Passando a SQL e a conexão
'Comando.Parameters.AddWithValue("@Nome", "Teste")
Comando.ExecuteNonQuery()
Comando.Dispose()
End If
Next
Catch ex As Exception
'lblmsg.Text = ex.Message + vbCrLf + queries(a)
Return False
End Try
'lblmsg.Text = "FIM"
'btnExecutar.Enabled = True
Return True
End Function
Preenche uma DataGrid com o conteúdo de uma pesquisa do MS ACCESS
''' <summary>
''' Executa um SQL que retorna dados num Datagrid
''' </summary>
''' <param name="Querie"></param>
''' <param name="tabela"></param>
''' <returns>um datagrid</returns>
''' Exemplo : Querie = "Select * from Clientes" e tabela = Clientes
Public Function ExecutaSQL(Querie As String, tabela As String) As DataGrid
Dim strcon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Meus Docs DeskTop\Visual Studio 2010\Projects\129-AcessoAccess\AcessoAccess.mdb;"
Dim Con As New OleDb.OleDbConnection(strcon)
Dim sql As String = Querie
Dim da As New OleDb.OleDbDataAdapter(sql, Con)
Dim ds As New DataSet
Dim dg As New DataGrid
da.Fill(ds, tabela) 'como desejo chamar a tabela
dg.DataSource = ds.Tables(tabela) 'qual tabela desejo do dataset
dg.Refresh()
Application.DoEvents()
Con.Close()
da.Dispose()
ds.Dispose()
Return dg
End Function
'#############################################################################################################
'# as funçãoes abaixo mencionadas executam todas as funcionalidades de acesso as bases de dados ACCESS
'#############################################################################################################
''' <summary>
''' Para acessar os objetos ADODB precisa Projeto, Adicionar Referencia, Assemblies, Extensions, ADODB
''' </summary>
Definição do String de conexão com o ACCESS
Public BDCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Ref_Tec_Visual_Studio_2010\RefTecVS2010.mdb;Jet OLEDB:Database Password=Presidencialismo;" ' String de conexão com banco de dados
Public bd As ADODB.Connection ' banco de dados
Public rs As ADODB.Recordset ' conjunto de registros retornados de uma tabela do banco de dados
Public sql As String ' pesquisa a ser feita no banco de dados
Public erro As String
Função que executa uma querie no MS ACCESS retornando seu resultado num DataSet
Public Function BDretornaDataSet(ByVal query As String) As DataSet
'NÃO utilizar com o DataGridView
erro = ""
Try
Dim ds As New DataSet
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(ds)
Conexao.Close()
Return ds
' dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Função que executa uma querie no MS ACCESS retornando seu resultado num DataAdapter 1
Public Function BDretornaDataAdapter(ByVal query As String) As OleDb.OleDbDataAdapter
'testado ok
'não usar como fonte do DataGRidView
erro = ""
Try
Dim datatb As New DataTable
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(datatb)
Debug.Print(datatb.Rows.Count)
Conexao.Close()
Return dataadp
' dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Função que executa uma querie no MS ACCESS retornando seu resultado num DataAdapter 2
Public Function BDretornaTabelaDataAdapter(ByVal query As String, ByVal Tabela As String) As OleDb.OleDbDataAdapter
erro = ""
Try
Dim datatb As New DataTable
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(datatb, Tabela)
Conexao.Close()
Return dataadp
' dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Função que executa uma querie no MS ACCESS retornando seu resultado num DataTable
Public Function BDretornaDataTable(ByVal query As String) As DataTable
'utilizar com o DataGridView
erro = ""
Try
Dim datatb As New DataTable
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Dim dataadp As New OleDb.OleDbDataAdapter(query, BDCon)
dataadp.Fill(datatb)
Conexao.Close()
Return datatb
'dataadp.Dispose()
Catch ex As Exception
erro = ex.Message
Return Nothing
End Try
End Function
Função que executa uma querie no MS ACCESS que não retorna dados
É o caso de uma Drop Table ou um delete. Só retorna o status.
''' <summary>
''' Executa uma querie que não retorna dados - tipo delete
''' </summary>
''' <param name="query"></param>
Public Sub BDExecutaComando(ByVal query As String)
erro = ""
Try
Dim Conexao As New OleDb.OleDbConnection(BDCon)
Conexao.Open()
Dim comando As New OleDbCommand(query, Conexao)
comando.ExecuteNonQuery()
Conexao.Close()
Catch ex As Exception
erro = ex.Message
End Try
End Sub
End Module