IN YOUR OPINION

[DS]

This updates a database that isn't the current one.
Does anyone see any problems with this or any way to make it shorter?
I have to place it in a lot of places so your advice and/or suggestions
are most welcome.
Thanks
DS

If Nz(DLookup("BackActive", "tblBackPath", "BackID=1"), 0) = -1 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & DLookup("BackName", "tblBackPath",
"BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "'," & _
"table1.IDName2 = '" & Forms!Form1!TxtInfo2 & "'," & _
"table1.IDName3 = ' " & Forms!Form1!TxtInfo3 & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

 

[Ed Metcalfe]

This updates a database that isn't the current one.
Does anyone see any problems with this or any way to make it shorter?
I have to place it in a lot of places so your advice and/or suggestions
are most welcome.
Thanks
DS

If Nz(DLookup("BackActive", "tblBackPath", "BackID=1"), 0) = -1 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & DLookup("BackName", "tblBackPath",
"BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "'," & _
"table1.IDName2 = '" & Forms!Form1!TxtInfo2 & "'," & _
"table1.IDName3 = ' " & Forms!Form1!TxtInfo3 & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

DS,

My only comment would be that I prefer the CurrentDB.Execute method (with
the dbFailOnError option) of running an action query. If you use this method
you won't need to turn warnings off/on.

Ed Metcalfe.

 

[Albert D. Kallal]

A few things:

We executing the Dlookup two times. While the first execution will "pull"
the value into memory, and then caching should reduce the time for the 2nd
dlooup, but really, you should be only executing that lookup ONCE.

Remember, that dlooup is a sql statement. That means:

- ms-access has to setup a connection, and open he table
- load the sql query processor
- parse out the field names, verity the syntax
- build a query plan to determine the best method of data revival
- grab the data.

The above is actually a SHORT version of actually what occurs. so, a LOT
things occur to execute that dlookup.

On a fast modern machine, your network is STILL the same speed it was 10
years ago. You can only execute about 10, perhaps 20 sql or dlookup
statements in one second (you likely grab 100,000 records with one sql
statement in less then a second, but EACH statement is VERY expensive in
terms of time.


So, this is one case where I would spend the above processing time to load
up the reocreset, and if it found..then you can just update it.

In your cause, your executing 3 sql statements (2 lookups, and a update
statements).

We can condense this down to ONE sql statement.

Dim rst As DAO.Recordset
Dim strSql As String

strSql = "select * from tblBackPath where backid = -1" & _
" and BackActive = -1"

Set rst = CurrentDb.OpenRecordset(strSql)
If rst.RecordCount > 0 Then
rst.Edit
rst!IDName = Me.TxtInfo
rst!IDName2 = Me.TxtInfo2
rst!IDName3 = Me.txtinfo3
rst.Update
End If
rst.Close


It is assumed the above code is running in the current form, and thus I use
me.TxtInfo, as it not necessary to use the full forms qualified name (and,
thus if you re-name the form, or copy this code..it will continue to
function without need to be modified for the correct form name).

In many cases for updating *lots* of records, use sql statements is
preferable, but for one record, it often an advantage to use a reocrdset,
especially when the loading of the reocrdset is also your search criteria as
above to see if the record actually exists.

And, also, when you use recordsets, you don't have to "form" sql syntax and
bother with all the quotes. For obth numbers, and text, and even null
assignments...you can use regual VBA code as a above. So, it cleaner to
write, and less code in this case...

 

[DS]

Hi Ed,
Wow, something done right! Thank you.
BTW I was always under the impression that the DStuff need a field to
write to or the = sign
Me.TxtBox = DStuff(...)
or in a textbox
=DStuff(...) this doesn't seem to be the case, am I wrong or misguided?
Thanks
DS

 

[DS]

Thanks Albert I appreciate the input. That's alot going on in that
little statement and your code looks like much faster...
I tried and got a few errors.

Dim rst As DAO.Recordset
Dim strSql As String

strSql = "SELECT * FROM tblBackPath WHERE backid = 1" & _
" and BackActive = -1"


'**********************************************************************
'THE strSQL s the path to the new database that wil be written to...
'IDName is one of the fields in the new database
'Me.TxtInfo is the field on the current form
'I check what the string is giving and it'sot giving the path, the path
'is in a field called BackPath in the tblBackPath, right now I just get
'the whole select statement. It's also saying that the IDNAME line is
'not in the current collection.
'**********************************************************************


Set rst = CurrentDb.OpenRecordset(strSql)
If rst.RecordCount > 0 Then
rst.Edit
rst!IDName = Me.TxtInfo
rst!IDName2 = Me.TxtInfo2
rst!IDName3 = Me.TxtInfo3
rst.Update
End If
rst.Close

Thank you once again your expertise is appreciated.
DS

 

[DS]

Just for chuckles I tried this and I think the problem is that it's not
referencing the table.

Set rst = CurrentDb.OpenRecordset("C:\ProRed\Current.mdb\Table1")
If rst.RecordCount > 0 Then
rst.Edit
rst!IDName = Me.TxtInfo
rst!IDName2 = Me.TxtInfo2
rst!IDName3 = Me.TxtInfo3
rst.Update
End If
rst.Close

DS

 

[DS]

Ok, I came up with 2 options both of which work. Which is faster and
more efficent?

NUMBER 1

Dim strPATH As String
Dim Test2SQL As String
strPATH = Nz(DLookup("BackName", "tblBackPath", "BackID=1 AND
BackActive=-1"), "")
If strPATH > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPATH & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If


NUMBER 2

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = Nz(DLookup("BackName", "tblBackPath", "BackID=1 AND
BackActive=-1"), "")
If strSQL > "" Then
Set db = OpenDatabase(strSQL)
Set rst = db.OpenRecordset("Table1")

rst.Edit
rst!IDName = Me.TxtInfo
rst!IDName2 = Me.TxtInfo2
rst!IDName3 = Me.TxtInfo3
rst.Update
rst.Close
Set rst = Nothing
db.Close
ElseIf strSQL = "" Then
End If

Thanks
DS

 

[DS]

Ok, I came up with 3 options all of which work. Which is faster and
more efficent?

NUMBER 1

Dim strPATH As String
Dim Test2SQL As String
strPATH = Nz(DLookup("BackName", "tblBackPath", "BackID=1 AND
BackActive=-1"), "")
If strPATH > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPATH & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If


NUMBER 2

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = Nz(DLookup("BackName", "tblBackPath", "BackID=1 AND
BackActive=-1"), "")
If strSQL > "" Then
Set db = OpenDatabase(strSQL)
Set rst = db.OpenRecordset("Table1")

rst.Edit
rst!IDName = Me.TxtInfo
rst!IDName2 = Me.TxtInfo2
rst!IDName3 = Me.TxtInfo3
rst.Update
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
ElseIf strSQL = "" Then
End If

AND OLD NUMBER 3

If Nz(DLookup("BackActive", "tblBackPath", "BackID=1"), 0) = -1 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & DLookup("BackName", "tblBackPath",
"BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "'," & _
"table1.IDName2 = '" & Forms!Form1!TxtInfo2 & "'," & _
"table1.IDName3 = ' " & Forms!Form1!TxtInfo3 & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

Thanks
DS

 

[Bob Quintal]

Just for chuckles I tried this and I think the problem is that
it's not referencing the table.

of course it isn't. you are specifying to open a table named
C:\ProRed\Current.mdb\Table1 that exists in the current database.

Try
strSQL = "SELECT * from table 1 in C:\ProRed\Current.mdb"
Set rst = CurrentDb.OpenRecordset(strSQL)

I know that works.

Set rst = CurrentDb.OpenRecordset("C:\ProRed\Current.mdb\Table1")
If rst.RecordCount > 0 Then
rst.Edit
rst!IDName = Me.TxtInfo
rst!IDName2 = Me.TxtInfo2
rst!IDName3 = Me.TxtInfo3
rst.Update
End If
rst.Close

DS

 

[Bob Quintal]

Thanks Bob,
I'm not sure if you can use the select to set a string. I keep
getting this as strSQL "SELECT * from table 1 in
C:\ProRed\Current.mdb" Perhaps a DLookUP.
Thanks
DS

Not sure if table 1 has an extra space in it that's confusing the SQL
parser, but review the help file on SELECT to confirm that it does
work. C:\ProRed\Current.mdb also needs to be in quotes.

"SELECT * from table_1 in 'C:\ProRed\Current.mdb' "

 

[DS]

Thanks Bob,
I'm not sure if you can use the select to set a string. I keep getting
this as strSQL "SELECT * from table 1 in C:\ProRed\Current.mdb"
Perhaps a DLookUP.
Thanks
DS

 

[DS]

Not sure if table 1 has an extra space in it that's confusing the SQL
parser, but review the help file on SELECT to confirm that it does
work. C:\ProRed\Current.mdb also needs to be in quotes.

"SELECT * from table_1 in 'C:\ProRed\Current.mdb' "

Also tried this...
"SELECT * frm Table1 in "C:\ProRed\Current.mdb"
I'm still getting "SELECT * frm Table1 in "C:\ProRed\Current.mdb"
in the string.
Thanks
DS

 

[Bob Quintal]

Also tried this...
"SELECT * frm Table1 in "C:\ProRed\Current.mdb"
I'm still getting "SELECT * frm Table1 in "C:\ProRed\Current.mdb"
in the string.
Thanks
DS

What are you hoping to get out of the string?
It seems to be returning exactly what it should.

 

[Albert D. Kallal]

'**********************************************************************
'THE strSQL s the path to the new database that wil be written to...
'IDName is one of the fields in the new database

Right, I skimmed the code a bit too fast. All my brain saw was two dlooups()
to the SAME table. Just "habit" when reading code to spot possbile redundant
code.

So, you *do* need to pull that table references out of that dlooup. However,
we can *still* eliminate one dlooup, and use sql.

eg:

dim rst as dao.RecordSet
dim strSql as string

strSql = "select BackName from tblBackPath were BackActive = -1"
set rst = currentdb.openRecordSet(strSql)
if rst.RecordCount > 0 then
strSql = "UPDATE table1 IN '" & rst!BackName & "' " & _
"SET IDName = '" & me!TxtInfo & "'," & _
"IDName2 = '" & me!TxtInfo2 & "'," & _
"IDName3 = ' " & me!TxtInfo3 & "' " & _
"WHERE IDNumber = 1;"
currentdb.Execute strSql
end if
rst.close

Since you "update" is to external different tables, then your use of sql in
place of a reocrset is better. (in fact, I don't even think you can use a
external table name when building recordsets).

The only help the above gives is elimination of the one dlookup. Not a big
deal unless the code was being called a lot of times in a loop.

In other words, if the "speed" of this code is really only the user
interface and person clicking on abuttion..then optimzing as above is not
going to yeild some real beniefits here.

So, at the end of the day, your code as was is fine. The above does
eliminate one dlookup, but really, that elimination is not going to make or
break the application, and if the code is being run in response of a user
action, then you not going to gain a lot.

What you had is fine.....

 

[DS]

Thanks Albert,
Well, I must say your observation got me going all weekend to eliminate
those DLookups. Upon your suggestion I made the two into one. Then, I
figured out the record set thing, That worked. Then not to be content, I
eliminated the DLookup altogether at the form level with what I thought
should be a class module. Well Douglas pointed me towards a regular
module, except it wouldn't work, Turned out it needed one line, with
Georges help that line was added. Then back to Douglas he helped me get
it down to something realy simple, No declarations, Dlookups etc. Just
an If statement.

If Len(REDACT()) > 0 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & REDACT() & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

The Function*******************************
Public Function REDACT() As String
Dim strPath As String
strPath = Nz(DLookup("BackName", "tblBackPath", "BackID =1 AND
BackActive=-1"), "")
REDACT = strPath
End Function


A thing of beauty!
Thank You Douglas, Albert, and George!