On Thu, 6 Jan 2011 12:34:31 -0800 (PST), bfryman <bfry...@gmail.com>
wrote:
>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:
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?
>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
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.
>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
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
...
> MsgBox "Beginning the database update."
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