Very Computer

I am trying to replace an entire table that exists in my Access database using VB6.  I have completely updated the table with new records and need to send out the update to everyone using the database.  The table name, etc. will remain the same, it's just has all new records that are inside of it.

Does anyone know how to do this?  

Thanks!

Quote:>I am trying to replace an entire table that exists in my Access database using VB6.  I have completely updated the table with new records and need to send out the update to everyone using the database.  The table name, etc. will remain the same, it's just has all new records that are inside of it.

>Does anyone know how to do this?  

Several methods come to mind, each a variation on the same theme
(delete current records, write in new), but dependent on how you plan
to "distribute" your change and how many records. So we really need a
bit more information.

First off you said "Access database". Is everyone using this database
doing so with MS Access? Or is this simply a Jet-formatted database
file.

How many records are there? Less than 25 or something much larger?

Who are these users? People within your own organization (same net)?
Or customers scattered about the planet?

The obvious solution (for someone sitting next to you and just a few
records) is to simply build a little program that queries the table,
deletes the records, then inserts the new records (the data recorded
in the program). Copy the program to a floppy or to a common location
and ask the user to retrieve and run it. Apparently there is some
reason you either can't or don't want to do that. <g>

Details, details. <g>

-ralph

Sorry, I guess I should have given a little more info.  Our program uses an Access database that is distributed to customers all over the country as part of our software package.  The table I want to change currently has 6 records in it, but I am changing it to have 30 records.  Our program was written in VB6 and we no longer have a programmer on staff so that is why I am trying to figure out how to do it.  I have pretty limited knowledge of VB6 and SQL back to my college days b/c I never really used it since then.

Basically, I want to run a little VB6 executable that deletes the table and then re-adds it with the updated records.  Does that help?  I have been able to figure out to update/add individual records within a table, but I thought it might be easier to just update the table all at once.  Does that help any?

Thanks!

Quote:>Sorry, I guess I should have given a little more info.  Our program uses an Access database that is distributed to customers all over the country as part of our software package.  The table I want to change currently has 6 records in it, but I am changing it to have 30 records.  Our program was written in VB6 and we no longer have a programmer on staff so that is why I am trying to figure out how to do it.  I have pretty limited knowledge of VB6 and SQL back to my college days b/c I never really used it since then.

>Basically, I want to run a little VB6 executable that deletes the table and then re-adds it with the updated records.  Does that help?  I have been able to figure out to update/add individual records within a table, but I thought it might be easier to just update the table all at once.  Does that help any?

There are a few more questions:
1) Are these records going to share the same structure?
(ie, the column names, number, and attributes remain the same?)
2) Do these records have any associations with other tables?
This determines whether you will need to also manage the database
through DDL.
(DDL provides management tools for deleting and adding tables,
columns, attributes, associations, etc. But more on that later...)

Your first decision is which data access library to use. Your options
are DAO or ADO.

DAO has the advantage of being quicker and more straight-forward. It
also has DDL capability built-in.

ADO is more useful in a distributed shared environment, but it doesn't
include any DDL. To supply that you need to use the data access
extention library (ADOX) along with ADO.

Since this is a stand-alone program to be used once and not shared,
DAO would work very well for you.

If the only thing that is going to change is the data in the records,
then I wouldn't bother deleting the existing table, merely delete all
the records and then add new ones. This way you don't need to bother
with any DDL.

And while it might seem like a simple batch push of 30 records would
be quicker and simpler, for a project like this, IMHO, it probably
wouldn't be worth it. So you could just do a raw brute-force procedure
within your program and edit and test all in one place. After all 30
records aren't that many and it would all be essentially boil-plate
and surprisingly fast. (Computers execute dull boring stuff very well.
<g>)

So IMHO, (again assuming the record structure is the same), just use
DAO. [Warning! Air Code follows!)

Dim db As DAO.Database
Dim ws As DAO.WorkSpace
Dim rs As DAO.Recordset

   ' you will likely need to massage this a bit, eg is it password
protected?
Set ws = DBEngine.WorkSpaces(0)
Set db = ws.OpenDatabase ("theDatabase", False, False)
Set rs=db.OpenRecordset("tblMyTable", dbOpenDynaset)

    ' dump the old records
On Error Goto Delete_Err
With rs
      If .BOF = True And .EOF = True Then Exit Sub
      .MoveFirst
      Do While .EOF = False
         .Delete
         .MoveNext
      Loop
End With
 ...
Delete_Err:
    MsgBox "Error" & Str$(Err.Number) & _
        " delete failed" & vbCrLf & _
        Err.Description

   ' now insert the new records
Dim sTmp As String

        ' Create an INSERT query.
    sTmp = "INSERT INTO tblMyTable VALUES("
    sTmp = sTmp & "'" & sValueOne & "', "
    sTmp= sTmp & "'" & sValueTwo & "', "
    sTmp = sTmp & "'" & sValueThree & "'"
    sTmp = sTmp & ")"

       ' Execute the query.
    On Error GoTo Insert_Err
    db.Execute query
   ...
Insert_Err:
    MsgBox "Error" & Str$(Err.Number) & _
        " insert failed" & vbCrLf & _
        Err.Description

Of course this is just a raw example. You will probably want to have
the strings already built in your code and perhaps stick them in an
array. Also the error handlers would have to be relocated etc.

You can also use a CSV file (comma-delimited) to do it in one go with
DAO.
http://www.vb-helper.com/howto_dao_csv_to_mdb.html
VBHelper has a ton of examples.

hth
-ralph

To answer your questions:

1. The records will have the exact same structure
2. The table has nothing to do with any other tables

What you sent me makes sense.  I did just find though some old code our old programmer used to use when updating the database with a new record in a table.  Is there anyway I could just manipulate this?  It looks like he uses that ADO you were talking about instead of DAO (which seems simpler, oh well).  Here is the code he used, I've edited it a little to make it more general:

Private Sub Form_Load()
Me.Hide
Call updatedb
End Sub
Private Sub updatedb()
 Dim sMsg As String
 Dim strsql As String
 Dim strDbConn As String
 Dim strsource As String
 Dim ors As New ADODB.Recordset

On Error Resume Next
    With CommonDialog1
        .DialogTitle = "Select Database to Update"
        .DefaultExt = "mdb"
        .Filter = "Client Database(*.mdb)|*.mdb"
        .InitDir = "C:\"
        .Action = 1

    End With

    strsource = CommonDialog1.FileName
    If strsource = "" Then
        MsgBox "Please rerun this application and select your database."
        End
    End If

    strDbConn = "DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;Password=1234;DBQ=" & strsource

    MsgBox "Beginning the database update."

  ors.Open "Select * From table", strDbConn, 1, 3
'this adds the new record to the database
'replace the OLD 2009 numbers below with the actual return numbers for 2010
       ors.AddNew
           ors.Fields("column1").Value = 1111
           ors.Fields("column2").Value = 2222
       ors.Update
 ors.Close

 Set ors = Nothing

MsgBox "Finished with updating your database."
End
End Sub

thanks for your help so far!
brad

On Thu, 6 Jan 2011 12:34:31 -0800 (PST), bfryman <bfry...@gmail.com>
wrote:

Ok, that's fine.

Made a few comments inline below.

And one more question (lol)
This is a Jet 4.0 formatted database file - ".mdb"? Correct?

     NEVER use the "As New" construct in production code.
     It is VB's equivalent of Declaring and Defining in one line, as
opposed to  ...
       Dim ors As ADODB.Recordset
       Set ors = New ADODB.Recordset
    It looks simple enough, and appears to be saving a few keystrokes,
unfortunately it can cause un-intended problems.

What happens when you declare an object reference this way is that VB
will automatically insert hidden code before EVERY line that
references the object to check if the object has been set and if not
it will instance a new object and set it for you.

So for example if you write ...
     Dim rs As New ADODB.Recordset
     ors.Open "Select * From table", strDbConn, 1, 3
     ors.AddNew
     ors.Fields("column1").Value = 1111
     ors.Update
     ors.Close
     Set ors = Nothing

What you really get is ...
     Dim rs As New ADODB.Recordset
     If ors Is Nothing Then Set ors = New ADODB.Recordset
     ors.Open "Select * From table", strDbConn, 1, 3
     If ors Is Nothing Then Set ors = New ADODB.Recordset
     ors.AddNew
     If ors Is Nothing Then Set ors = New ADODB.Recordset
     ors.Fields("column1").Value = 1111
     If ors Is Nothing Then Set ors = New ADODB.Recordset
     ors.Update
     If ors Is Nothing Then Set ors = New ADODB.Recordset
     ors.Close
     If ors Is Nothing Then Set ors = New ADODB.Recordset
     Set ors = Nothing

That's a lot of wasted clicks.

But the bigger issue is that last line. You will set the object to
Nothing, but it doesn't really matter cause if you reference the
variable again you will silently get a new one. During development
errors of that type are useful to know ... but "As New" will disguise
it ... you'll never know till runtime if you are dealing with a valid
object or not.

The programmer for whatever reason is using the Jet Native 'driver'
which is essentially an ODBC driver. ADO is based on OLE DB and not
ODBC. It just so happens that in this case everything will work itself
out -  the default 'driver' for ADO is the OLE DB Provider for ODBC.
But, it is better to specify an OLE DB 'Provider' instead of depending
on the 'Driver=' default.

  "Provider=Microsoft.Jet.OLEDB.4.0;"

ADO is dependent on having a Connection objection.
When using ADO in your application create one Connection Object (New)
and leave it until the application exits then set it to Nothing. But
.Open and .Close the connection object just before and just after you
use it.

1) The first thing you need is a connection string. We'll use the old
one as the example.
Dim strDbConn As String
strDbConn =
"Provider=Microsoft.Jet.OLEDB.4.0;UID=admin;Password=1234;DBQ=" &
strsource

[Hint: You can go online and find various connection string examples,
for various situations. www.Connectionstrings.com
But there is a very simple way to create and test you connection
string, plus manage additional attributes, and that is to use the
Universal Data Link Dialog.
http://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx
When you are done testing and selecting options. Save it. Then you can
re-open the file in Notepad and there is your connection string,
custom configured for your needs, ready to be copied and pasted into
your program.]

2) Now we open the connection and make sure its good
Dim cn As ADODB.Connection
On Error Goto Connection_Err
Set cn = New ADODB.Connection
With cn
   .ConnectionString = strDbConn
   .Open
End if
Connection_Err:
    ' error code stuff here
    ' by capturing the VB error and
    ' interating the cn.Errors collection
3) Now the Recordset
We need to set up the cursor, locks, ...
You can can do this within the open command or in many cases just
accept the defaults. but I like to spell it out ... saves a lot of
headaches seeing it in black 'n white - opposed to making assumptions.
Which is what the original programmer did. <g>

On Error Goto Recordset_Err
Dim ors As ADODB.Recordset
Set ors = New ADODB.Recordset
With ors
   .CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockBatchOptimistic
   .Open "Select * From TableOfInterest", cn
End With
Recordset_Err:
    ' error handling code here
    ' by capturing the VB error and
    'interating the cn.Errors collection

[Note: There are other ways to get a recordset with a tad less typing.
For example, you could the Execute command.
Set ors = cn.Execute("Select * From TableOfInterest")
Or even a command object.
But these methods are best if used on a known system, or within a
known environment. If you are sending something out to an unknown
users miles away it is best to do it one step at a time.
For example, if you opened a recordset the way  the original
programmer wrote it, and the open failed - you wouldn't be sure what
was the problem. But by going step by step you can more quickly
isolate the problem and get a repair out to the user.]

4) Delete the records
' once again setup a new error handler
On Error Goto Delete_Err
With rs
      If .BOF = True And .EOF = True Then Goto Delete_Err
      .MoveFirst
      Do While .EOF = False
         .Delete
         .MoveNext
      Loop
      .UpdateBatch adAffectAll
End With

' if you want to check and make sure all records are gone ...
  ors.Filter = adFilterNone
  ors.Requery
   ...

Ok, here I'm going to go into a bit of psuedocode ... as you will
probably re-arrange this to fit.
You can use what he has ... or any of the other examples I posted.

On Error Goto Add_Err
With ors
   ' Some kind of counter loop or For Each ... etc
   Do while array still have stuff or whatever ...
       .AddNew
        .Fields("column1").Value = arr(i)
        .Fields("column2").Value = arr(i,)
        i = i + 1
   Loop
   .UpdateBatch adAffectAll
   .Close
End With
      cn.Close
Set rs = Nothing
Set cn = Nothing

Whew!

HTH. lol

I just tried to highlight some of the features that many of the
articles leave out. You can find a ton of information and examples on
the web by just typing in the key objects - eg. "ADO Connection
Object", "ADO Recordset Object", etc. Most of them will be far better
that what I typed.

I tried to type accurately but I never tested the code so be aware of
typos and out-right errors on my part. <g>

-ralph