Delete jpg file based on Access field contents

Hello,

What would be the best way to have access delete a bunch of jpg's?

I have two tables in mdb format, the main table has 36000 records. When I am 
done with a record, I move it to a Deleted Record table (using a query and a 
macro). There are about 1300 of the deleted records that have an associated 
jpg file. The file size is around 100k, so I have around 130 meg of files 
cluttering up my laptop. I will never look at the jpg's again. I know it's 
not a lot of space, but why not have some code get rid of them?

So, should I use a button on a regularly accessed Form, or put code in the 
query that moves the records to the new Table? Also, any pointers to code 
would be greatly appreciated.

Thanks,
Mark
0
Utf
12/27/2009 7:51:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

19 Replies
776 Views

Similar Articles

[PageSpeed] 6

tstew wrote:
>Hello,
>
>What would be the best way to have access delete a bunch of jpg's?
>
>I have two tables in mdb format, the main table has 36000 records. When I am 
>done with a record, I move it to a Deleted Record table (using a query and a 
>macro). There are about 1300 of the deleted records that have an associated 
>jpg file. The file size is around 100k, so I have around 130 meg of files 
>cluttering up my laptop. I will never look at the jpg's again. I know it's 
>not a lot of space, but why not have some code get rid of them?
>
>So, should I use a button on a regularly accessed Form, or put code in the 
>query that moves the records to the new Table? Also, any pointers to code 
>would be greatly appreciated.
>
>Thanks,
>Mark

You can't use a query to delete objects that are external to Access.  But you
could loop through the records in the Deleted Record table and use the Kill
statement to delete the jpg files from there.

something like
dim rsPix as dao.recordset
set rsPix = DbEngine(0)(0).OpenRecordset("tblDeletedRecords",
dbOpenForwardOnly)

' delete the jpg files associated with records to be deleted
do until rsPix.EOF
    Kill rsPix!PathToJPG
    rsPix.MoveNext
loop
rsPix.close
set rsPix = nothing

' now delete the records in the junk table
DBEngine(0)(0).Execute "DELETE * FROM tblDeletedRecords"

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
12/27/2009 10:48:58 PM
<picky>
You might want to check that the file actually exists before trying to 
delete it.

Do Until rsPix.EOF
  If Len(Dir(rsPix!PathToJPG)) > 0 Then
    Kill rsPix!PathToJPG
    rsPix.MoveNext
  End If
Loop
</picky>

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"PieterLinden via AccessMonster.com" <u49887@uwe> wrote in message 
news:a13c0f94df950@uwe...
> tstew wrote:
>>Hello,
>>
>>What would be the best way to have access delete a bunch of jpg's?
>>
>>I have two tables in mdb format, the main table has 36000 records. When I 
>>am
>>done with a record, I move it to a Deleted Record table (using a query and 
>>a
>>macro). There are about 1300 of the deleted records that have an 
>>associated
>>jpg file. The file size is around 100k, so I have around 130 meg of files
>>cluttering up my laptop. I will never look at the jpg's again. I know it's
>>not a lot of space, but why not have some code get rid of them?
>>
>>So, should I use a button on a regularly accessed Form, or put code in the
>>query that moves the records to the new Table? Also, any pointers to code
>>would be greatly appreciated.
>>
>>Thanks,
>>Mark
>
> You can't use a query to delete objects that are external to Access.  But 
> you
> could loop through the records in the Deleted Record table and use the 
> Kill
> statement to delete the jpg files from there.
>
> something like
> dim rsPix as dao.recordset
> set rsPix = DbEngine(0)(0).OpenRecordset("tblDeletedRecords",
> dbOpenForwardOnly)
>
> ' delete the jpg files associated with records to be deleted
> do until rsPix.EOF
>    Kill rsPix!PathToJPG
>    rsPix.MoveNext
> loop
> rsPix.close
> set rsPix = nothing
>
> ' now delete the records in the junk table
> DBEngine(0)(0).Execute "DELETE * FROM tblDeletedRecords"
>
> -- 
> Message posted via http://www.accessmonster.com
> 

0
Douglas
12/27/2009 10:54:28 PM
<picky>

Do Until rsPix.EOF
  If Len(Dir(rsPix!PathToJPG)) > 0 Then
    Kill rsPix!PathToJPG
    rsPix.MoveNext
  End If
Loop

Why: -
If Len(Dir(rsPix!PathToJPG)) > 0 Then
when: -
If Len(Dir(rsPix!PathToJPG)) Then
does the same thing.
Len(Dir(rsPix!PathToJPG))
returns 0 or a number greater than 0.
0 is equal to False and anything other than 0 is true.

or

why not simply
On Error Resume Next
since, if the file doesn’t exist at that location then there is no need to 
Kill it.
and
rsPix!PathToJPG
might evaluate to Null which raises a type mismatch error on Dir(Null)

</picky>

-- 
A nod is as good as a wink to a blind horse.


"Douglas J. Steele" wrote:

> <picky>
> You might want to check that the file actually exists before trying to 
> delete it.
> 
> Do Until rsPix.EOF
>   If Len(Dir(rsPix!PathToJPG)) > 0 Then
>     Kill rsPix!PathToJPG
>     rsPix.MoveNext
>   End If
> Loop
> </picky>
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> 
> "PieterLinden via AccessMonster.com" <u49887@uwe> wrote in message 
> news:a13c0f94df950@uwe...
> > tstew wrote:
> >>Hello,
> >>
> >>What would be the best way to have access delete a bunch of jpg's?
> >>
> >>I have two tables in mdb format, the main table has 36000 records. When I 
> >>am
> >>done with a record, I move it to a Deleted Record table (using a query and 
> >>a
> >>macro). There are about 1300 of the deleted records that have an 
> >>associated
> >>jpg file. The file size is around 100k, so I have around 130 meg of files
> >>cluttering up my laptop. I will never look at the jpg's again. I know it's
> >>not a lot of space, but why not have some code get rid of them?
> >>
> >>So, should I use a button on a regularly accessed Form, or put code in the
> >>query that moves the records to the new Table? Also, any pointers to code
> >>would be greatly appreciated.
> >>
> >>Thanks,
> >>Mark
> >
> > You can't use a query to delete objects that are external to Access.  But 
> > you
> > could loop through the records in the Deleted Record table and use the 
> > Kill
> > statement to delete the jpg files from there.
> >
> > something like
> > dim rsPix as dao.recordset
> > set rsPix = DbEngine(0)(0).OpenRecordset("tblDeletedRecords",
> > dbOpenForwardOnly)
> >
> > ' delete the jpg files associated with records to be deleted
> > do until rsPix.EOF
> >    Kill rsPix!PathToJPG
> >    rsPix.MoveNext
> > loop
> > rsPix.close
> > set rsPix = nothing
> >
> > ' now delete the records in the junk table
> > DBEngine(0)(0).Execute "DELETE * FROM tblDeletedRecords"
> >
> > -- 
> > Message posted via http://www.accessmonster.com
> > 
> 
> .
> 
0
Utf
12/28/2009 1:28:02 AM
There was a bug in previous versions of Access where you could run into 
problems if you didn't actually have a comparison in an If statement, so 
it's a coding convention I always follow. And while you're correct that

  If Len(Dir(rsPix!PathToJPG)) > 0 Then

and

  If Len(Dir(rsPix!PathToJPG)) Then

are syntactically the same, the first is more correct. (It also handles the 
extremely unlikely possibility that VBA could someday change the fact that 
it treats all non-zero values as True to only accepting -1 as True)

And while On Error Resume Next would indeed work, it would mean that other 
legitimate errors would not be caught.

I'll grant that the possibility of a Null value for PathToJPG would cause an 
error. I should have instantiated rsPix to only return non-Null values:

  set rsPix = DbEngine(0)(0).OpenRecordset( _
    "SELECT PathToJPG FROM tblDeletedRecords " & _
    "WHERE PathToJPG IS NOT NULL", dbOpenForwardOnly)

(That's also a better practice, since it brings back a smaller recordset)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
news:A8252538-C941-4998-A530-4063F6D8B04D@microsoft.com...
> <picky>
>
> Do Until rsPix.EOF
>  If Len(Dir(rsPix!PathToJPG)) > 0 Then
>    Kill rsPix!PathToJPG
>    rsPix.MoveNext
>  End If
> Loop
>
> Why: -
> If Len(Dir(rsPix!PathToJPG)) > 0 Then
> when: -
> If Len(Dir(rsPix!PathToJPG)) Then
> does the same thing.
> Len(Dir(rsPix!PathToJPG))
> returns 0 or a number greater than 0.
> 0 is equal to False and anything other than 0 is true.
>
> or
>
> why not simply
> On Error Resume Next
> since, if the file doesn’t exist at that location then there is no need to
> Kill it.
> and
> rsPix!PathToJPG
> might evaluate to Null which raises a type mismatch error on Dir(Null)
>
> </picky>
>
> -- 
> A nod is as good as a wink to a blind horse.
>
>
> "Douglas J. Steele" wrote:
>
>> <picky>
>> You might want to check that the file actually exists before trying to
>> delete it.
>>
>> Do Until rsPix.EOF
>>   If Len(Dir(rsPix!PathToJPG)) > 0 Then
>>     Kill rsPix!PathToJPG
>>     rsPix.MoveNext
>>   End If
>> Loop
>> </picky>
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "PieterLinden via AccessMonster.com" <u49887@uwe> wrote in message
>> news:a13c0f94df950@uwe...
>> > tstew wrote:
>> >>Hello,
>> >>
>> >>What would be the best way to have access delete a bunch of jpg's?
>> >>
>> >>I have two tables in mdb format, the main table has 36000 records. When 
>> >>I
>> >>am
>> >>done with a record, I move it to a Deleted Record table (using a query 
>> >>and
>> >>a
>> >>macro). There are about 1300 of the deleted records that have an
>> >>associated
>> >>jpg file. The file size is around 100k, so I have around 130 meg of 
>> >>files
>> >>cluttering up my laptop. I will never look at the jpg's again. I know 
>> >>it's
>> >>not a lot of space, but why not have some code get rid of them?
>> >>
>> >>So, should I use a button on a regularly accessed Form, or put code in 
>> >>the
>> >>query that moves the records to the new Table? Also, any pointers to 
>> >>code
>> >>would be greatly appreciated.
>> >>
>> >>Thanks,
>> >>Mark
>> >
>> > You can't use a query to delete objects that are external to Access. 
>> > But
>> > you
>> > could loop through the records in the Deleted Record table and use the
>> > Kill
>> > statement to delete the jpg files from there.
>> >
>> > something like
>> > dim rsPix as dao.recordset
>> > set rsPix = DbEngine(0)(0).OpenRecordset("tblDeletedRecords",
>> > dbOpenForwardOnly)
>> >
>> > ' delete the jpg files associated with records to be deleted
>> > do until rsPix.EOF
>> >    Kill rsPix!PathToJPG
>> >    rsPix.MoveNext
>> > loop
>> > rsPix.close
>> > set rsPix = nothing
>> >
>> > ' now delete the records in the junk table
>> > DBEngine(0)(0).Execute "DELETE * FROM tblDeletedRecords"
>> >
>> > -- 
>> > Message posted via http://www.accessmonster.com
>> >
>>
>> .
>> 

0
Douglas
12/28/2009 12:46:58 PM
Well, we could sit here all day re-writing code but one thing I would not do 
is use dbOpenForwardOnly.

dbOpenForwardOnly requires a reference to DAO so I would use: -


Option Explicit
Option Compare Text


Public Const conOpenForwardOnly As Long = 8


Sub TestIt()

    With CurrentDb.OpenRecordset(" SELECT PathToJPG FROM tblDeletedRecords" 
& _
                                 " WHERE PathToJPG IS NOT NULL", 
conOpenForwardOnly)
        On Error Resume Next
        
        Do Until .EOF
            Kill !PathToJPG
            .MoveNext
        Loop
        
        .Close
    End With

End Sub

And in that loop perhaps set !PathToJPG = Null to reduce the Query hit.


But at this stage I don’t know why ‘deleted’ records are being moved rather 
than just marked as being not available. Or, if ‘deleted’ is the correct word 
then just delete the records.

So, until more information is available, I’m just guessing.

Regards,
Chris.


-- 
A nod is as good as a wink to a blind horse.


"Douglas J. Steele" wrote:

> There was a bug in previous versions of Access where you could run into 
> problems if you didn't actually have a comparison in an If statement, so 
> it's a coding convention I always follow. And while you're correct that
> 
>   If Len(Dir(rsPix!PathToJPG)) > 0 Then
> 
> and
> 
>   If Len(Dir(rsPix!PathToJPG)) Then
> 
> are syntactically the same, the first is more correct. (It also handles the 
> extremely unlikely possibility that VBA could someday change the fact that 
> it treats all non-zero values as True to only accepting -1 as True)
> 
> And while On Error Resume Next would indeed work, it would mean that other 
> legitimate errors would not be caught.
> 
> I'll grant that the possibility of a Null value for PathToJPG would cause an 
> error. I should have instantiated rsPix to only return non-Null values:
> 
>   set rsPix = DbEngine(0)(0).OpenRecordset( _
>     "SELECT PathToJPG FROM tblDeletedRecords " & _
>     "WHERE PathToJPG IS NOT NULL", dbOpenForwardOnly)
> 
> (That's also a better practice, since it brings back a smaller recordset)
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> 
> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
> news:A8252538-C941-4998-A530-4063F6D8B04D@microsoft.com...
> > <picky>
> >
> > Do Until rsPix.EOF
> >  If Len(Dir(rsPix!PathToJPG)) > 0 Then
> >    Kill rsPix!PathToJPG
> >    rsPix.MoveNext
> >  End If
> > Loop
> >
> > Why: -
> > If Len(Dir(rsPix!PathToJPG)) > 0 Then
> > when: -
> > If Len(Dir(rsPix!PathToJPG)) Then
> > does the same thing.
> > Len(Dir(rsPix!PathToJPG))
> > returns 0 or a number greater than 0.
> > 0 is equal to False and anything other than 0 is true.
> >
> > or
> >
> > why not simply
> > On Error Resume Next
> > since, if the file doesn’t exist at that location then there is no need to
> > Kill it.
> > and
> > rsPix!PathToJPG
> > might evaluate to Null which raises a type mismatch error on Dir(Null)
> >
> > </picky>
> >
> > -- 
> > A nod is as good as a wink to a blind horse.
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> <picky>
> >> You might want to check that the file actually exists before trying to
> >> delete it.
> >>
> >> Do Until rsPix.EOF
> >>   If Len(Dir(rsPix!PathToJPG)) > 0 Then
> >>     Kill rsPix!PathToJPG
> >>     rsPix.MoveNext
> >>   End If
> >> Loop
> >> </picky>
> >>
> >> -- 
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >>
> >> "PieterLinden via AccessMonster.com" <u49887@uwe> wrote in message
> >> news:a13c0f94df950@uwe...
> >> > tstew wrote:
> >> >>Hello,
> >> >>
> >> >>What would be the best way to have access delete a bunch of jpg's?
> >> >>
> >> >>I have two tables in mdb format, the main table has 36000 records. When 
> >> >>I
> >> >>am
> >> >>done with a record, I move it to a Deleted Record table (using a query 
> >> >>and
> >> >>a
> >> >>macro). There are about 1300 of the deleted records that have an
> >> >>associated
> >> >>jpg file. The file size is around 100k, so I have around 130 meg of 
> >> >>files
> >> >>cluttering up my laptop. I will never look at the jpg's again. I know 
> >> >>it's
> >> >>not a lot of space, but why not have some code get rid of them?
> >> >>
> >> >>So, should I use a button on a regularly accessed Form, or put code in 
> >> >>the
> >> >>query that moves the records to the new Table? Also, any pointers to 
> >> >>code
> >> >>would be greatly appreciated.
> >> >>
> >> >>Thanks,
> >> >>Mark
> >> >
> >> > You can't use a query to delete objects that are external to Access. 
> >> > But
> >> > you
> >> > could loop through the records in the Deleted Record table and use the
> >> > Kill
> >> > statement to delete the jpg files from there.
> >> >
> >> > something like
> >> > dim rsPix as dao.recordset
> >> > set rsPix = DbEngine(0)(0).OpenRecordset("tblDeletedRecords",
> >> > dbOpenForwardOnly)
> >> >
> >> > ' delete the jpg files associated with records to be deleted
> >> > do until rsPix.EOF
> >> >    Kill rsPix!PathToJPG
> >> >    rsPix.MoveNext
> >> > loop
> >> > rsPix.close
> >> > set rsPix = nothing
> >> >
> >> > ' now delete the records in the junk table
> >> > DBEngine(0)(0).Execute "DELETE * FROM tblDeletedRecords"
> >> >
> >> > -- 
> >> > Message posted via http://www.accessmonster.com
> >> >
> >>
> >> .
> >> 
> 
> .
> 
0
Utf
12/28/2009 9:26:01 PM
"ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
news:2A9E1BF2-FDD5-408B-9819-88C98294C15B@microsoft.com...
> Well, we could sit here all day re-writing code but one thing I would not 
> do
> is use dbOpenForwardOnly.
>
> dbOpenForwardOnly requires a reference to DAO so I would use: -

And I would never dream of using Access without a reference to DAO.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



0
Douglas
12/29/2009 8:25:32 PM
>>And I would never dream of using Access without a reference to DAO.<<

I’m not sure what that means, Doug, so perhaps you might like to explain 
that point a little further.

From my perspective, I try to remove all references (except the two that 
can’t be removed) and that includes the reference to DAO. That has worked 
well for me over the last 9 or 10 years so I’m a little perplexed at your 
statement that you ‘would never dream about it’.

So, do you mean that you never thought about it, and therefore would not 
dream about it? Or do you mean you have thought about it, decided not to do 
it, and have forgotten that you dreamed about it and made the assumption that 
you never dreamed about it?

From a humorous point of view, I don’t know if I’ve ever dreamt about it 
but, then again, my memory of my dreams is probably flawed. ;-)

Regards,
Chris.

-- 
A nod is as good as a wink to a blind horse.


"Douglas J. Steele" wrote:

> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
> news:2A9E1BF2-FDD5-408B-9819-88C98294C15B@microsoft.com...
> > Well, we could sit here all day re-writing code but one thing I would not 
> > do
> > is use dbOpenForwardOnly.
> >
> > dbOpenForwardOnly requires a reference to DAO so I would use: -
> 
> And I would never dream of using Access without a reference to DAO.
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
> 
> .
> 
0
Utf
1/1/2010 9:14:01 AM
Since the point of Access applications is almost always to deal with data, 
and since DAO is the preferred approach when the back-end is a Jet (or ACE) 
database, I would never create an Access front-end that didn't have a 
reference set to DAO (or ACE).

If my back-end is in another DBMS such as SQL Server, I might also set a 
reference to ADO in addition to the reference to DAO.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
news:0EE7EEB7-E5AE-4D41-B539-D5B388307706@microsoft.com...
>>>And I would never dream of using Access without a reference to DAO.<<
>
> I’m not sure what that means, Doug, so perhaps you might like to explain
> that point a little further.
>
> From my perspective, I try to remove all references (except the two that
> can’t be removed) and that includes the reference to DAO. That has worked
> well for me over the last 9 or 10 years so I’m a little perplexed at your
> statement that you ‘would never dream about it’.
>
> So, do you mean that you never thought about it, and therefore would not
> dream about it? Or do you mean you have thought about it, decided not to 
> do
> it, and have forgotten that you dreamed about it and made the assumption 
> that
> you never dreamed about it?
>
> From a humorous point of view, I don’t know if I’ve ever dreamt about it
> but, then again, my memory of my dreams is probably flawed. ;-)
>
> Regards,
> Chris.
>
> -- 
> A nod is as good as a wink to a blind horse.
>
>
> "Douglas J. Steele" wrote:
>
>> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message
>> news:2A9E1BF2-FDD5-408B-9819-88C98294C15B@microsoft.com...
>> > Well, we could sit here all day re-writing code but one thing I would 
>> > not
>> > do
>> > is use dbOpenForwardOnly.
>> >
>> > dbOpenForwardOnly requires a reference to DAO so I would use: -
>>
>> And I would never dream of using Access without a reference to DAO.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>>
>> .
>> 

0
Douglas
1/1/2010 4:39:53 PM
> Since the point of Access applications is almost always to deal with data, 
> and since DAO is the preferred approach when the back-end is a Jet (or 
> ACE) database, I would never create an Access front-end that didn't have a 
> reference set to DAO (or ACE)

Not to mention that Access itself uses DAO recordsets internally (which is 
as good a recommendation as any), so the overhead of a reference (which is 
what I think Chris is concerned about) will be negligible.

I wouldn't like to do without DAO, referenced or otherwise. Preferably 
referenced though, so I can use all that intellisense. Since it's included 
in all OS's these days, there isn't any trouble with references when 
distributing, either.


0
Stuart
1/1/2010 5:29:10 PM
I’m not suggesting not using DAO what I am suggesting is not having a 
reference to it. 
The following link should explain it better.

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=

I’m also well aware of the immediate response most long-term programmers 
have to it but that response tends to fade over time.

Regards,
Chris.

-- 
A nod is as good as a wink to a blind horse.


"Douglas J. Steele" wrote:

> Since the point of Access applications is almost always to deal with data, 
> and since DAO is the preferred approach when the back-end is a Jet (or ACE) 
> database, I would never create an Access front-end that didn't have a 
> reference set to DAO (or ACE).
> 
> If my back-end is in another DBMS such as SQL Server, I might also set a 
> reference to ADO in addition to the reference to DAO.
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> 
> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
> news:0EE7EEB7-E5AE-4D41-B539-D5B388307706@microsoft.com...
> >>>And I would never dream of using Access without a reference to DAO.<<
> >
> > I’m not sure what that means, Doug, so perhaps you might like to explain
> > that point a little further.
> >
> > From my perspective, I try to remove all references (except the two that
> > can’t be removed) and that includes the reference to DAO. That has worked
> > well for me over the last 9 or 10 years so I’m a little perplexed at your
> > statement that you ‘would never dream about it’.
> >
> > So, do you mean that you never thought about it, and therefore would not
> > dream about it? Or do you mean you have thought about it, decided not to 
> > do
> > it, and have forgotten that you dreamed about it and made the assumption 
> > that
> > you never dreamed about it?
> >
> > From a humorous point of view, I don’t know if I’ve ever dreamt about it
> > but, then again, my memory of my dreams is probably flawed. ;-)
> >
> > Regards,
> > Chris.
> >
> > -- 
> > A nod is as good as a wink to a blind horse.
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message
> >> news:2A9E1BF2-FDD5-408B-9819-88C98294C15B@microsoft.com...
> >> > Well, we could sit here all day re-writing code but one thing I would 
> >> > not
> >> > do
> >> > is use dbOpenForwardOnly.
> >> >
> >> > dbOpenForwardOnly requires a reference to DAO so I would use: -
> >>
> >> And I would never dream of using Access without a reference to DAO.
> >>
> >> -- 
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >>
> >> .
> >> 
> 
> .
> 
0
Utf
1/1/2010 10:13:01 PM
"ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
news:FAF4E666-1D49-4B8E-926A-C4DDFBB481A8@microsoft.com...
> I'm not suggesting not using DAO what I am suggesting is not having a
> reference to it.
> The following link should explain it better.
>
> http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=
That's fine if you're just accessing the currently loaded db, but not for 
external db work.

I'm not suggesting not using DAO either, just that the presumed reference 
problems don't exist for this lib so you might as well make use of those 
handy drop-downs and pre-declared constants etc.

>
> I'm also well aware of the immediate response most long-term programmers
> have to it

I wonder why that would be?

> but that response tends to fade over time.

Lacking hard evidence to back up that claim, yours is just another opinion.

Happy new year!


0
Stuart
1/2/2010 1:08:50 AM
>>That's fine if you're just accessing the currently loaded db, but not for 
external db work.<<

Well, as I have already said, I am aware of most of the immediate responses 
posted by long-term programmers and that’s one of them. 

That response amounts to; let’s see if we can break the principle somewhere 
and that should prove that it shouldn’t be used anywhere. What it amounts to 
is a failure when applying ‘reductio ad absurdum’ as a principle in that the 
principal is extrapolated but the logic is not followed. In other words, the 
test has become flawed during the extrapolation.

Here is where the extrapolation is flawed: -
I mentioned the term ‘long-term programmers’ because ‘long-term programmers’ 
build re-usable code libraries. Those code libraries should contain re-usable 
code down to the procedure level. Each procedure should be as independent as 
it can be and not rely on outside influences. 

Most outside influences are handled by late binding because late binding 
does not require a reference. If we late bind other references then why not 
late bind DAO as well?

It’s at the procedure level that late binding occurs, not at the project 
level. A late bound procedure can function equally well in a project that, 
over all, might require a reference. But the late bound procedure does not 
contribute to the requirement for that project to have that reference.

The application of ‘reductio ad absurdum’ was flawed in this case because 
the logic broke when trying to move the argument from within the re-usable 
procedure to areas where both the procedure, and the logic, does not apply.

I’ll give an example: -
If you read the link I posted then copy/paste Public Sub Case1() into a new 
Access 2K database, it should fail. Now copy/paste Public Sub Case2() into 
the same database, it should work. 

Why does Public Sub Case1() fail and Public Sub Case2() work? 
It’s because Access 2K did not have a reference set to DAO but it does have 
a hidden reference to DAO. 
From the A2K help file: -
Note  In Microsoft Access the CurrentDb method establishes a hidden 
reference to the Microsoft DAO 3.6 Object Library in a Microsoft Access 
database (.mdb).

That ‘hidden reference’ works all the way to A2K7 and it is no way a ‘slight 
of hand’ because it was put there by Microsoft.

Next question is; how long will it work into the future? 
For that answer you will have to ask Microsoft, but don’t expect a reply. I 
think it will be there as long as DAO is there and if DAO is removed then the 
whole argument becomes a moot point.

If you have an individual procedure that must use a reference to DAO, and 
can’t be converted to late bound, then fine just use it. But why have a 
procedure that requires a reference if that procedure can be written in a way 
that does not require one? If you write re-usable code for a code library why 
compromise that procedure with external references?

A good re-usable procedure should be unto itself.

-- 
A nod is as good as a wink to a blind horse.


"Stuart McCall" wrote:

> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
> news:FAF4E666-1D49-4B8E-926A-C4DDFBB481A8@microsoft.com...
> > I'm not suggesting not using DAO what I am suggesting is not having a
> > reference to it.
> > The following link should explain it better.
> >
> > http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=
> That's fine if you're just accessing the currently loaded db, but not for 
> external db work.
> 
> I'm not suggesting not using DAO either, just that the presumed reference 
> problems don't exist for this lib so you might as well make use of those 
> handy drop-downs and pre-declared constants etc.
> 
> >
> > I'm also well aware of the immediate response most long-term programmers
> > have to it
> 
> I wonder why that would be?
> 
> > but that response tends to fade over time.
> 
> Lacking hard evidence to back up that claim, yours is just another opinion.
> 
> Happy new year!
> 
> 
> .
> 
0
Utf
1/2/2010 5:06:01 AM
Please excuse me, Stuart, because I failed to answer one of your questions, 
namely; “I wonder why that would be?”

That is a very difficult question indeed and one that I would prefer not to 
answer in full, or at least to the best of my ability however good that may 
be.

However, my answer would centre on what might be called ‘web ego’. It’s 
about protecting perceived status, which largely has been earned, but removes 
from the recipient of such status the ability to think in a way that may 
contradict what they have said in the past to achieve such status.

I would not like your question, or my answer, to get in the way of a 
technical reply so may I suggest we stick to the technical problems.

If you have a technical question, point or objection I would like to hear it 
and perhaps I may be able to help or at least clarify my position.


-- 
A nod is as good as a wink to a blind horse.


"Stuart McCall" wrote:

> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
> news:FAF4E666-1D49-4B8E-926A-C4DDFBB481A8@microsoft.com...
> > I'm not suggesting not using DAO what I am suggesting is not having a
> > reference to it.
> > The following link should explain it better.
> >
> > http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=
> That's fine if you're just accessing the currently loaded db, but not for 
> external db work.
> 
> I'm not suggesting not using DAO either, just that the presumed reference 
> problems don't exist for this lib so you might as well make use of those 
> handy drop-downs and pre-declared constants etc.
> 
> >
> > I'm also well aware of the immediate response most long-term programmers
> > have to it
> 
> I wonder why that would be?
> 
> > but that response tends to fade over time.
> 
> Lacking hard evidence to back up that claim, yours is just another opinion.
> 
> Happy new year!
> 
> 
> .
> 
0
Utf
1/2/2010 9:04:01 AM
"ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
news:77B83D29-A8CC-400C-9DCC-03FE5BFBC908@microsoft.com...
> Please excuse me, Stuart, because I failed to answer one of your 
> questions,
> namely; "I wonder why that would be?"
>
> That is a very difficult question indeed and one that I would prefer not 
> to
> answer in full, or at least to the best of my ability however good that 
> may
> be.
>
> However, my answer would centre on what might be called 'web ego'. It's
> about protecting perceived status, which largely has been earned, but 
> removes
> from the recipient of such status the ability to think in a way that may
> contradict what they have said in the past to achieve such status.
>
> I would not like your question, or my answer, to get in the way of a
> technical reply so may I suggest we stick to the technical problems.
>
> If you have a technical question, point or objection I would like to hear 
> it
> and perhaps I may be able to help or at least clarify my position.

Not interested enough to continue this. Take it up with someone who cares.


0
Stuart
1/2/2010 11:11:56 AM
=?Utf-8?B?Q2hyaXNP?= <ChrisO@discussions.microsoft.com> wrote in
news:FAF4E666-1D49-4B8E-926A-C4DDFBB481A8@microsoft.com: 

> I� Tm not suggesting not using DAO what I am suggesting is not
> having a reference to it. 
> The following link should explain it better.
> 
> http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number
> =1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20
> 036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp= 
> 
> I� Tm also well aware of the immediate response most long-term
> programmers have to it but that response tends to fade over time.

Why would you want to use DAO basically with late binding? You lose
all the data types, though you can still create DAO objects through
CurrentDB (as the example you cite shows). When you lose the data
types you also lost the Intellisense that makes them easy to work
with. 

It's also the case that recordsets created that way don't have the
same scope, so you can't always do it without a variable. In that
case you'd have to use an Object variable, and this will be slower. 

I think it's insane to remove the DAO reference and still use DAO.
You can do it, but why I simply cannot imagine. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
1/2/2010 10:50:00 PM
"Stuart McCall" <smccall@myunrealbox.com> wrote in
news:eH6jsg0iKHA.4912@TK2MSFTNGP02.phx.gbl: 

> "ChrisO" <ChrisO@discussions.microsoft.com> wrote in message 
> news:FAF4E666-1D49-4B8E-926A-C4DDFBB481A8@microsoft.com...
>> I'm not suggesting not using DAO what I am suggesting is not
>> having a reference to it.
>> The following link should explain it better.
>>
>> http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Numbe
>> r=1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=
>> 20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp= 
>
> That's fine if you're just accessing the currently loaded db, but
> not for external db work.

Actually, you don't need the DAO reference to work with databases
other than CurrentDB, since there's a top-level Application.DBEngine
object that exposes all the functionality you need from there. 

But again, I can't fathom why one would want to make one's coding so
difficult. I see no benefit to it at all. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
1/2/2010 10:53:38 PM
=?Utf-8?B?Q2hyaXNP?= <ChrisO@discussions.microsoft.com> wrote in
news:AD384F38-F279-415B-80AD-FC3EBDE07BA2@microsoft.com: 

> If we late bind other references then why not 
> late bind DAO as well?

Your example is absurd. This code won't be running outside of
Access, so there's no point in coding around the idea that a
component won't be available in Access when that component is
fundamental to Access's operation. 

Secondly, the A2K example is ludicrous -- that was a remarkably
stupid design error on Microsoft's part and they changed it. 

Third, code portability is not a valid goal except for the code that
is intended to be portable. The DAO reference is hardly a block to
portability significant enough to abandon al the benefits in terms
of ease-of-use and performance that the DAO reference provides. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
1/2/2010 10:58:03 PM
David.

I think I answered you points in the post 5 up from yours but I’m really not 
sure. 

So let’s have another go at it and let’s also try to remove emotive words.

>>I think it's insane to remove the DAO reference and still use DAO.<<

That might be correct under some certain circumstances; however the 
direction of logic flow might be wrong. As with other forms of late binding 
it can be written as early binding and then move to late binding when done. 
This would mean we still use DAO and then remove the DAO reference which is 
the reverse of what is quoted.

The ‘why’ of doing so I think I have already posted. We late bind in order 
to try and preserve portability. DAO was not referenced in Access 2000 so any 
code that used variables declared as DAO.SomethingOrOther would fail because 
of the lack of a DAO reference in Tool>References. Yes it’s easy to fix, but 
why have to fix something that needed not to be broken in the first place?

So where possible at the procedure level, we can write it in such a way that 
it works no matter where it’s used. This to means the code is better because 
it’s more portable but there is more to it than just that.

From the link I provided: -

Public Sub Case2()
 
    With CurrentDb.OpenRecordset("tblMyTable", 2)
        Do Until .EOF
            MsgBox .Fields("SomeText")
            .MoveNext
        Loop
        .Close
    End With
 
End Sub

That should run in any version of Access from 97 and up.
So the question becomes; why compromise that portability by declaring a 
variable that requires as DAO reference? 

The other point, and really the reason I started writing it that way a lot 
of years ago, is that we circumvent the old hackney argument about having to 
set variables to nothing when finished with them. In the above case the 
argument is circumvented because there is no variable set to something and, 
therefore, no requirement to set it to nothing.

So, if I’m allowed to be a little humours here, I’m not being argumentative, 
I’m being non- argumentative. ;-)

-----------------

In any case, this but a small part of something much larger.
The larger thing is about writing code in such a way that tries to work 
under all circumstances, and that‘s not an easy task.


We, apart from some people who happen to live in a country which runs the 
default regional settings, need to watch for date settings in SQL. And yes, 
it’s been done to death (and appropriately so by people like Allen Browne and 
others) yet it is still often ignored. To me it seems the most often asked 
question by Access people outside the US but all they really have to do is 
throw Allen’s function into the database and use it. But it also happens to 
be important for people inside the US because the code they write might work, 
if no one has changed their regional settings, but their code could break if 
moved to another country. But here again the procedure is the same. Try not 
to allow a code procedure to be dependant on outside influences.


Most of us use a period as the decimal separator and it generally works by 
default. But again it’s based on regional settings and a VBA SQL string build 
could fail under German (Germany) regional settings where the default is a 
comma. The code could fail because the procedure has become dependant on 
outside influences.


Some people use the word Detail when referring to a section on a Form or 
Report. But Detail is just the default name in the English version of Access 
and it can be changed. In the Norwegian version of Access I don’t think by 
keyboard would allow me to even type the default name of the detail section 
and it could change. The word Detail could fail, almost impossible to test, 
but the procedure has become dependant on outside influences.


If a procedure receives a Boolean argument set to -1 then the procedure will 
insert that Boolean argument as True in a VBA SQL string build. That works by 
default in the English version of Access. In the Dutch version of Access the 
word Waar is inserted and the SQL string blows. If all you are using is an 
English version of Access you can’t see that happening and you can’t de-bug 
it. The procedure has become dependant on outside influences. 


Some charts will work fine if we pay attention to Dates including most 
regional settings. Those same charts can fail with Afrikaans. I don’t know 
why and I couldn’t fix it using Date/Time fields. The fix was to stop using 
Date/Time fields and use straight doubles. Somehow the procedures had become 
dependant on an outside influence.



The point being, try not to write a procedure that is dependant on an 
outside influence and not even make the assumption that the reference to DAO 
will be present.

-- 
A nod is as good as a wink to a blind horse.


"David W. Fenton" wrote:

> =?Utf-8?B?Q2hyaXNP?= <ChrisO@discussions.microsoft.com> wrote in
> news:FAF4E666-1D49-4B8E-926A-C4DDFBB481A8@microsoft.com: 
> 
> > Iƒ Tm not suggesting not using DAO what I am suggesting is not
> > having a reference to it. 
> > The following link should explain it better.
> > 
> > http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number
> > =1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20
> > 036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp= 
> > 
> > Iƒ Tm also well aware of the immediate response most long-term
> > programmers have to it but that response tends to fade over time.
> 
> Why would you want to use DAO basically with late binding? You lose
> all the data types, though you can still create DAO objects through
> CurrentDB (as the example you cite shows). When you lose the data
> types you also lost the Intellisense that makes them easy to work
> with. 
> 
> It's also the case that recordsets created that way don't have the
> same scope, so you can't always do it without a variable. In that
> case you'd have to use an Object variable, and this will be slower. 
> 
> I think it's insane to remove the DAO reference and still use DAO.
> You can do it, but why I simply cannot imagine. 
> 
> -- 
> David W. Fenton                  http://www.dfenton.com/ 
> usenet at dfenton dot com    http://www.dfenton.com/DFA/
> .
> 
0
Utf
1/3/2010 2:16:01 AM
=?Utf-8?B?Q2hyaXNP?= <ChrisO@discussions.microsoft.com> wrote in
news:878D7133-7DD4-4180-9E8A-6F7008CBA103@microsoft.com: 

[quoting me:]
>>>I think it's insane to remove the DAO reference and still use
>>>DAO.<< 
> 
> That might be correct under some certain circumstances; however
> the direction of logic flow might be wrong. As with other forms of
> late binding it can be written as early binding and then move to
> late binding when done. This would mean we still use DAO and then
> remove the DAO reference which is the reverse of what is quoted.

???

Not "use" as in "writing code" but "use" as in "at runtime the code
executes using DAO." 

Late binding is slower than early binder, both in initializing and
in traversing properties/methods. Now, it may not be enough slower
to matter, but the lack of any justification for late binding DAO in
the first place makes this performance sacrifice useless. And it
*could* make a difference in particular types of scenarios. 

> The ‘why’ of doing so I think I have already posted. We late
> bind in order to try and preserve portability.

And there's absolutely no portability issue in Access.

> DAO was not referenced in Access 2000 so any 
> code that used variables declared as DAO.SomethingOrOther would
> fail because of the lack of a DAO reference in Tool>References.
> Yes it’s easy to fix, but why have to fix something that needed
> not to be broken in the first place? 

It should be fixed because any A2K app that lacks the DAO reference
is written by someone who doesn't know what they are doing. Either
they didn't use DAO in code before that point or they are using ADO.
In either case, their app is wrongly-designed for what they are
trying to do, precisely because they didn't know they need to
rectify Microsoft's design error in not setting the default DAO
reference. 

> So where possible at the procedure level, we can write it in such
> a way that it works no matter where it’s used. This to means the
> code is better because it’s more portable but there is more to
> it than just that. 

I disagree that this is a priority that should drive the design of
code. 

[]

> That should run in any version of Access from 97 and up.
> So the question becomes; why compromise that portability by
> declaring a variable that requires as DAO reference? 

Because it's better design. It will be more understandable by more
programmers. It will execute faster. It will compile more reliably.
It will be more easily maintained. It will use memory more
efficiently. 

> The other point, and really the reason I started writing it that
> way a lot of years ago, is that we circumvent the old hackney
> argument about having to set variables to nothing when finished
> with them. In the above case the argument is circumvented because
> there is no variable set to something and, therefore, no
> requirement to set it to nothing. 

But your example works in only one type of circumstance. There are
numerous cases where it does *not* work. I'm all for using With
blocks where they make sense, but as your method of avoiding DAO
early binding, it seems misguided. 

Indeed, if I were going with your approach, I'd still cache a
reference to CurrentDB as I do with normal early binding of DAO,
rather than needing to set up the With block all the time. 

Also, to me, the chief loss of late binding is strongly typed
variables for your workspaces and databases and recordsets and
fields. This makes the code harder to debug and less efficient when
it runs. Yes, of course, you could do your programming with early
binding and switch to late binding for production, but why bother?
There is no gain whatsoever to doing so except for the silly
portability argument you're making. 

> So, if I’m allowed to be a little humours here, I’m not being
> argumentative, I’m being non- argumentative. ;-)

I think your argument is insanely unrealistic and misguided and will
say so at length in order to try to prevent your leading someone
down the garden path. 

> In any case, this but a small part of something much larger.
> The larger thing is about writing code in such a way that tries to
> work under all circumstances, and that‘s not an easy task.

I think this is an overblown artifical requirement that you're
applying to a coding environment that is very stable and entirely
reliable with early binding (and the one case in which it is not is
fixable in 1 second -- anyone who doesn't know how to do that
shouldn't be copying VBA code from elsewhere into their current
app). 

[completely irrelevant examples of SQL compatibility deleted]

> The point being, try not to write a procedure that is dependant on
> an outside influence and not even make the assumption that the
> reference to DAO will be present.

You're comparing a 747 to a gnat. 

There is absolutely no significant issue with portability for code
writting using early binding in DAO. The only minor issue you've
adduced (the A2000 issue) is so easily rectified that I just don't
understand why you're recommending that someone spend so much more
time coding with late binding (or wasting the time of switching from
early to late binding for production) just to save the time of
adding the DAO reference in any A2000-created MDB that lacks it. 

Your priorities are just completely upside-down.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
1/3/2010 9:09:05 PM
Reply:

Similar Artilces:

Importing .ics Files Into Calendar
I have downloaded the new version 12 of Windows Live Calendar desktop and have been sent several .ics files by email to import into the calendar. The instructions tell me to click on 'Import' > 'Browse' to find the files which I have copied to a folder on the computer and import them straight into the calendar. However, there is no 'Import' that I can find. I am using Windows XP Pro version with all the latest updates. Cheers Alt + F, Import. -- Ron Sommer MS MVP-Mail "cliff" <cliff@discussions.microsoft.com> wrote in message n...

Opening Access without Menus displayed
Access 97 I have created a simple data lookup form for a customer to look up his own jobs in a shared database - there is only the one form in the mdb(e) that auto-opens on application startup. I have unchecked all the Startup functions etc. The program auto-opens the form okay but there is the default menu bar displayed across the top of the application window. The tool bars do not show. Is there a way to open the program and not show ANY menus as well? My form close event also quits the application. I intend to provide this as an mde. Any ideas gratefully appreciated Piri Piri wrote: &...

Content in user's calendar different than other calendars
Good morning from Wisconsin, In our network, one user's calendar is not seeing the same events/ notes as everyone else. The updates that he puts in don't get pushed out to the shared calendars, and he can see some of the updates that other users put into the shared calendars but not all updates. It appears as if his calendars won't update beyond a date somewhere in the neighborhood of about three weeks ago. However, he can send/ receive email from the server just fine. The clients are all running Outlook 2003 and log into Active Directory on a Windows SBS 2003 box running Exch...

How to write to the registry in Vista from Access
Hi... This is my first post and I want to know if there is somewhere a code to write to the registry. I have one from someone in the past but I try to use it on Windows Vista and instead of putting the key in the local machine/software it is adding to some virtual folder. Does anyone knows why??? Thanks... This is not really an Access question, but a Vista one. My best guess though is that you need administrative privileges to write to the registry, and Vista by default does not let anyway have those privileges without specifically logging in that way. -- Arvin Meyer, MCP, MVP ht...

Can I delete OE and just use Microsoft Outlook 2002
I would like to delete Outlook Express is this advisable? I am going to be using Microsoft Outlook 2002 which I recieved w/Toshiba PDA and already have it set up on the same PC as OE. Can I use Microsoft Outlook for multiple e- mail accounts? Differences Between Outlook and Outlook Express http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257824 With Outlook, you can integrate and manage e-mail from multiple e-mail accounts, personal and group calendars, contacts, and tasks. "Delia Marlowe" <anonymous@discussions.microsoft.com> wrote in message news:780901c4025...

How to open a pub file without having Publisher
I am building a website for a friend and he sent me a file with the extension ..pub which I'm figuring is a Publisher file. The file is a brochure for a camp event to be held in April 2008. My problem is that I do not have Publisher on my computer. I have looked in MS Office Suite which I have, and there is Word, Excel, PP, etc. but no Publisher. Is there a MS application that will open this file, as all I need is the information about the camp? Any help would be appreciated. Thanks in advance. Christy wrote: > I am building a website for a friend and he sent me a file with ...

Save as in Access 2002
I have a user who is running Office XP Pro on Windows XP Pro and when she tries to export an Access table to Excel she does not have Excel as an option in the drop down list. Any ideas on how to fix this? Look for the file Msexcl40.dll on the harddrive (it should be in the System folder), and use regsvr32.exe to reregister it. This problem is discussed in http://support.microsoft.com/?id=209805 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "Jonathan Hearn" <jhearn@envirocon.com> wrote in message news:002301c3c0de$09859d10$a50...

Export to Text Files Based on Field Value
I have an Access table with personal information (e.g., first name, last name, birth date, etc.) for thousands of people. The table is sorted by state. I want to be able to run a macro on that table that will create separate pipe delimited text files for each state with all fields included. Any help would be appreciated? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 Instead of exporting the table, export a query based on the table. You will have to have some code that loops through the states and modifies the quer...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Cannot open earlier version of access database
I'm trying to open Northwind database with MS Access 2003. The problem is that a dialog box appears prompting me to go to Database > Tools > Convert Database, however, the dialog box re-appears over a couple of times and disallows the user from doing anything within MS Access. If you are getting the Convert dialog you will need to convert the database to the version that you are working in and save it and then open it next time instead of the original file. During the convert process it will not change the original file. It will make a NEW copy for you to use. I wonder w...

Accessing Money reconciled statements?
Is there any way to access past reconciled statements? For some reason, when I went to reconcile it says that my starting balance doesn't match my ending balance from the last statement. I thought I would find the last statement and enter that ending balance as my starting. Not as easy as I thought. Thanks in advance. You won't be able to do that - if the ending balance is modified, you're just shifting the problem back. You'll need to find where you have either deleted a reconciled transaction, or unreconciled a previously reconciled transaction and make an appropri...

Count Based on Comparison with Two Fields
I recently learned that I can count the number of items in one column that match items in another column by using: =COUNT(MATCH( 'Sheet1'!G2:G15001, 'Sheet2'!A2:A15, 0)) However, I now need to count records on one sheet in which TWO fields match items in both of two separate columns (i.e., a comparison of two columns to two columns and a count of records in the first that have fields matching elements in the second) . My attempt to use the above statement with an AND failed. Can someone explain how to do this? Hi I would suggest that you post a before a...

Delete a GL Posted
I want to delete a GL posted in the system. HOW TO DO THAT?????????? Consulta, Actually you cannot, but you can rollout the journal to disaffect the one you need to delete. There is always the database modifications workaround, which for sure is not recommended, you can find the Posted GL transactions in GL20000. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "Consulta" wrote: > > I want to delete a GL posted in the syste...

Is there a way to extract files from the Off line files cache
I have a laptop that used to be a member of a SBS 2003 domain and was utilizing folder redirection of My Documents. The laptop was never reconfigured to remove it from the domain and the user continued to use the domain login. Since that time (about a year ago), the computer and user were removed from the SBS domain. So far so good, the user on the laptop was happy.. Today, the laptop was worked on and was removed from the domain. Now, the question is, where are the files that used to be in My documents? I beleive they are cached in the Offline files folder (C:\wind...

Contacts File Link?
I have several computers on a home network and WLM does a fine job of keeping the contacts in sync on all the computer. I am giving one of the systems to a friend. What is the best way to un-sync the contacts file on the system that I am giving away so they can have there own contacts file? Uninstall WL and reinstall with a new WL ID or Passport? Create a new Windows user. Remove the old user. -- Ron Sommer MS MVP- Windows Live Mail "Ken" <krlorenz@comcast.net> wrote in message news:2099F8C5-9BCE-4229-A3D2-5187C1F4FFF9@microsoft.com... > I have several...

Auto Populate a form field
I have two tables with 1:1 relationship. Both have same key field (SS Number). There are two forms, one for each table. When I open Form A there is a button to open Form B. When Form B opens, I want SS Number field to equal the SS Number on the current record in Form A. How do I do this? Thank you in advance. Well, IMO the easiest way would be to make Form B a subform of A, and linking via the common key. Or you could make Form A a tabbed form, and A would be the first tab and B would be the second. Just a couple of random thoughts... "A Bit Frustrated&quo...

CRM4
Hi, On CRM4, I obtain an ASP.NET error while trying to access the website of my default organization. The message error is the following : "Caller does not have enough privilege to set CallerOriginToken to the specified value". I didn't find anything on the Web... Does anyone have an idea ? Fabien Majurel I have the same problem, I am going to reinstall and see if that helps Mike "Fabien Majurel" wrote: > Hi, > > On CRM4, I obtain an ASP.NET error while trying to access the website of my > default organization. The message error is the following...

Changing text based on criteria in an access report
Hello, I've got a textbox in a report, some records of which may contain text within { }. I need to find some way to format only that text (which may be in the middle of a string) so that it appears underlined and so that the brackets disappear. To illustrate, I posted a 5KB graphic here: http://img219.imageshack.us/img219/3523/jexamplemb2.png Does anybody have any suggestions or ideas? Thanks in advance! Mitch Access 2007 can format the text for you, using standard HTML for the underlining. Use the Replace() function to swap the braces for the HTML tags. Earlier versions of Ac...

A program is trying to access e-mail #2
When I hit new or reply I get this box saying, "A program is trying to access e-mail addresses you stored in Outlook. Do you want to allow this?" Symantec says it's a Microsoft problem. Microsoft says its a security patch sp3? How do I stop this dam message from poping up???? Outlook 2002 SP3 adds additional properties to the list of those that are affected by the security features. As a result, one of your add-ins needs updated to the latest version - antispam add-ins are a common cause although others are affected by the changes as well. Many add-ins were updated follow...

Has anyone linked MS Access and MS Money?
Greetings. I have a registration database for a school that uses MS Access. Our book keeper uses Quicken, so that all the student names, addresses, etc. needs to be entered twice. I am thinking of migrating the financial records to MS Money, so that we can do some VBA programming or the like to send student data from Access to Money. Does anyone know if this can be done, or have any experience doing this sort of thing? Thanks! Gary See http://umpmfaq.info/faqdb.php?q=10 in specific and http://umpmfaq.info/faqdb.php?cat=3 in general. "GaryDave" <garydave@hotmail.com> w...

How to delete a .mny file...
I'm new to Money 2003. I imported Quicken 2002 data, and for some reason, one or two accounts did not completely transfer. I repeated the import and opened a new Money file (with a new name.) This time it went smoothly. Simple question: can I just delete the old Money file I first created? Is it as simple as going to "my computer" and deleting the appropriate .mny file? Thanks for any response. EL In microsoft.public.money, EL wrote: >I'm new to Money 2003. I imported Quicken 2002 data, and >for some reason, one or two accounts did not completely >...

File Stuck In Outbox
I have a 6 MB file that can not be sent or deleted from my inbox. I followed the "Assistance" suggestions on the MS web site but had no luck. Any idea? http://www.outlook-tips.net/howto/recover_deleted.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/...

How to import Eudora files into Outlook
I've searched the forum, and everyone seems to have already been able to bring their old Eudora files into Outlook. I am now trying to do this but cannot figure out how. I'm not even sure exactly where to find my Eudora files, much less get them over to Outlook 2007. Can someone help me here. My hard drive crashed last Friday and I have rebuilt a new one except for my email program. I have been using Thunderbird temporarily, but I really don't like it. My Eudora files are saved to a hard drive and Thunderbird imported them with no problem. But I can't for the life of me figur...

Query to delete characters to the right of a decimal point
I need to write a query to help me clean up some letter codes in my database. The codes could have any of the following formats... XY001.01 AP002 SDFJ003.01 JUOI005 I would like the query to return everything to the left of the decimal point, so the data returned would look like this... XY001 AP002 SDFJ003 JUOI005 Thanks in advance for your help. -- Leslie M But didn't you give an example that has NO period in it? One way to approach this would be to use an IIF() statement that checks for a ".", perhaps something like: NewField: IIF(InStr([YourCode],".")...

Hyperlink has changed on file re-opening
[Excel 2002, Win XP Pro] Hi All, i have cells in a sheet whose cell contents are hyperlinked to wor documents. the hyperlink is generated using VBA. part of the fil name includes the '[' and ']' characters (square brackets) when the macro is run, everything works fine (click in one of the cell and the appropriate word file opens) BUT when the workbook is closed an re-opened, the '[' & ']' is changed to '%5b' and '%5d' respectivel (which i guess is some srt of charatcer code for those symbols) if the hyperlink macro is re-run, it 're...