VBA Code to copy a selected table

[ruthi12345]

Hey,
I am a new user in this forum. Seems a lot of activity going on. I am working on MS-Access at my work place. I have a list box in access form with various table names like "A 140230, A 120231,.. etc". When a particular table name is selected in the list box, I want that table to be copied as "master" table so that I may use a common query for my further operation. I would appreciate if someone could help me in doing this!! Please help me out!!

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ruthi12345]

Hi
Thanks for send out those links. I have written a VB code in Visual Studio for some other operation - " To export a query output to an excel spread sheet with certain rows highlighted in color". I tried to attach the same code to a button in Access form but I don't know how to do this. I would first like to understand how to attach a VB code to existing form before I actually get onto copying the tables. The code is as follows:
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Pilot_Insert_intoDataSet.common_qry' table. You can move, or remove it, as needed.
Me.Common_qryTableAdapter.Fill(Me.Pilot_Insert_intoDataSet.common_qry)

End Sub

Private Sub dgGridView_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles dgGridView.RowPostPaint
If e.RowIndex < Me.dgGridView.RowCount - 1 Then
Dim dgvRow As DataGridViewRow = Me.dgGridView.Rows(e.RowIndex)

If dgvRow.Cells(3).Value.ToString = "Strategic" Then
dgvRow.DefaultCellStyle.BackColor = Color.Green
End If

End If
End Sub

Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer

xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
Dim style As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("NewStyle")
style.Font.Bold = True
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green)

For i = 0 To dgGridView.RowCount - 2
For j = 0 To dgGridView.ColumnCount - 1
If (dgGridView(3, i).Value.ToString = "Strategic") Then
xlWorkSheet.Cells(i + 1, j + 1).Style = "NewStyle"
End If
xlWorkSheet.Cells(i + 1, j + 1) = _
dgGridView(j, i).Value.ToString()
Next
Next

xlWorkSheet.SaveAs("E:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

MsgBox("You can find the file E:\vbexcel.xlsx")
End Sub

Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class




Thanks!

 

[PHV]

attach the same code to a button in Access form but I don't know how to do this
1) your code is VB.NET not VBA
2) use the Click event procedure of your button for coding

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ruthi12345]

Thank you!

I will figure out..

 

[MajP]

If I understand you need a query called Master based on a table

Private Sub List0_AfterUpdate()
  Dim tblName As String
  Dim qdf As QueryDef
  Dim queryExists As Boolean
  Dim strSql As String
  tblName = "[" & List0.Value & "]"
  strSql = "select * from " & tblName
  For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = "Master" Then
      queryExists = True
      Exit For
    End If
  Next qdf
  If queryExists Then
    qdf.SQL = strSql
  Else
    Set qdf = CurrentDb.CreateQueryDef("Master", strSql)
    CurrentDb.QueryDefs.Append qdf
  End If
End Sub

 

[ruthi12345]

I am struggling to figure out how to convert these VB.net codes to VBA access. Every friend of mine says "NO ONE IS USING VBA TODAY". Do you suggest any way??

BTW thanks for the code!

 

[MajP]

Are your friends suggesting on building an Access application using .net libraries instead of VBA? I surely doubt Everyone is doing it. It not the easiest thing to do and there is some pitfalls. The pain can way outweigh the gain. Is this an Access application or a .net application using Access? What your code does and what your question is does not seem similar. What is the big picture?

You say tables in Access, but show working in a .net application and working with excel objects.
Why not a pure .net solution?