• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Want to copy existing record - VB/Access

Hello all!

I have an application with a VB6 front-end and an Access 97 back-end.  The primary table in the database is a general information Group table.  The Primary Key for the table is GroupNo.  I am looking for the best method to copy an existing record to a new record with the same GroupNo concatenated with a designated suffix.  This is quite easy entirely within Access using Insert SQL.  What is the best way in VB?


  • 4
  • 4
1 Solution
also use SQL...VB supports it....and since you used it in Access before, it should be easy for you......the syntax is exactly the same..you just need some general declarations before you issue SQL statements...contact me if you need the declaration codes...
lmerrellAuthor Commented:
Suppose I had a form with a combo box to select an existing GroupNo and a textbox for entry of the suffix.  I can build the sql to reference these controls with know problem.  Could you give me some example code to execute it.  I'm feeling real lazy today.  :-)


lmerrellAuthor Commented:
I meant "with no problem."  I had three people talking to me while I was typing my response.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

here's the code to setup the database:

Option Explicit
Const DBName As String = "\Tables.mdb"
Dim db As Database
Dim rs As Recordset
Dim strSQl As String
Dim I As Integer
Dim dbe As New DBEngine

Private Sub Form_Load()
    On Error GoTo LogErr
    'compact Database
    dbe.CompactDatabase App.Path & DBName, App.Path & "\CTable.mdb"
    Kill App.Path & DBName
    Name App.Path & "\CTable.mdb" As App.Path & DBName
    'open the database
    Set db = OpenDatabase(App.Path & DBName)
    Exit Sub
    lstSession.AddItem "Error: " & Err.Number & " (" & Err.Description & ")"
    'highlight the error message
    lstSession.Selected(lstSession.NewIndex) = True
    Resume Next
End Sub

Private Sub Form_Resize()
    On Error Resume Next
    If WindowState = vbMinimized Then
        Me.Visible = False
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
        'if you're using System Tray
        Dim lngRetVal As Long
        'return the correct windows handler just in case something went wrong
        lngRetVal = SetWindowLong(lngHwnd, GWL_WNDPROC, lngPrevWndProc)
        'remove icon from the System Tray
        Shell_NotifyIconA NIM_DELETE, nidTray
        UnloadAll           'unload all forms
        Cancel = 1
    End If
End Sub

'in the event you want, add the following code...for example Command1_Click()
        'change your SQL statement here....
        strSQl = "SELECT * FROM Users WHERE ULoginName Like '" & UserName & "'"
        Set rs = db.OpenRecordset(strSQl)
        If Not (rs.BOF And rs.EOF) Then
            Do While Not rs.EOF

                 'do your stuff here.....

      end if
      'skip this is you're going to leave the db open...
      set rs=nothing
      set db=nothing

by the way.."UnloadAll" is a function I wrote....it's not internal to VB....if you want the code (it's only a few lines)..msg me...
lmerrellAuthor Commented:
st_steve -

I was hoping to use an action query instead of doing this using a recordset through code.

What about using an Execute method?

mySQL = "INSERT INTO tblGroupStatic blah blah blah"
Set db = OpenDatabase(App.Path & DBName)
db.Execute mySQL

does this look right?
     You forgot to declare the type for lstSession since this is not a native VB type.
lmerrellAuthor Commented:
st_steve - FYI, the execute method does work.  In fact it works quite well.  I'm feeling generous and you did joustle me into thinking so...

"johnny6"....lstSession is a listbox to display the status of my program..other than that..it has no connection with DB or Access....

"lmerrell"..glad I can help :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now