Excel for Life: 25 new messages in 13 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Do I need a Nested Loop?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/70866bd7537ade3c?hl=en
==============================================================================

== 1 of 4 ==
Date: Fri, Nov 16 2007 4:57 am
From: "John Spencer"

Yes. On the first record phsNum = 0 and Prevphsnum = 0 so the LineCount
does not get set to 1 and remains at the default value of 0.

You can fix that by setting LineCount to 1 before you enter the loop.

Dim LineCount as Long
LineCount =1 'Set default value

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"nomadk" <nomadk@discussions.microsoft.com> wrote in message
news:EAFC820C-85D0-4EEA-8998-68A2A22C68E9@microsoft.com...
> Thanks John, I should have noticed that myself.
>
> I don't want to be a pest, but now I get:
>
> phsnum Match linnum
> 0 1100 0
> 0 2250 1
> 1 1100 1
> 1 1140 2
> 1 1230 3
> 3 1290 1
> 4 2280 1
>
> Can you tell me why the first record starts at "0", rather than "1"?

== 2 of 4 ==
Date: Fri, Nov 16 2007 5:28 am
From: nomadk

Perfect!

Thanks John and Doug for setting me straight.

"John Spencer" wrote:

> Yes. On the first record phsNum = 0 and Prevphsnum = 0 so the LineCount
> does not get set to 1 and remains at the default value of 0.
>
> You can fix that by setting LineCount to 1 before you enter the loop.
>
> Dim LineCount as Long
> LineCount =1 'Set default value
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "nomadk" <nomadk@discussions.microsoft.com> wrote in message
> news:EAFC820C-85D0-4EEA-8998-68A2A22C68E9@microsoft.com...
> > Thanks John, I should have noticed that myself.
> >
> > I don't want to be a pest, but now I get:
> >
> > phsnum Match linnum
> > 0 1100 0
> > 0 2250 1
> > 1 1100 1
> > 1 1140 2
> > 1 1230 3
> > 3 1290 1
> > 4 2280 1
> >
> > Can you tell me why the first record starts at "0", rather than "1"?
>
>
>

== 3 of 4 ==
Date: Fri, Nov 16 2007 5:31 am
From: "John Spencer"

Glad it is working.

Douglas did all the work. I just nit-picked.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"nomadk" <nomadk@discussions.microsoft.com> wrote in message
news:52BCA72F-C37E-4F55-8F1A-4A7EFD8A710A@microsoft.com...
> Perfect!
>
> Thanks John and Doug for setting me straight.
>
> "John Spencer" wrote:
>
>> Yes. On the first record phsNum = 0 and Prevphsnum = 0 so the LineCount
>> does not get set to 1 and remains at the default value of 0.
>>
>> You can fix that by setting LineCount to 1 before you enter the loop.
>>
>> Dim LineCount as Long
>> LineCount =1 'Set default value
>>
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "nomadk" <nomadk@discussions.microsoft.com> wrote in message
>> news:EAFC820C-85D0-4EEA-8998-68A2A22C68E9@microsoft.com...
>> > Thanks John, I should have noticed that myself.
>> >
>> > I don't want to be a pest, but now I get:
>> >
>> > phsnum Match linnum
>> > 0 1100 0
>> > 0 2250 1
>> > 1 1100 1
>> > 1 1140 2
>> > 1 1230 3
>> > 3 1290 1
>> > 4 2280 1
>> >
>> > Can you tell me why the first record starts at "0", rather than "1"?
>>
>>
>>

== 4 of 4 ==
Date: Fri, Nov 16 2007 7:35 am
From: "Douglas J. Steele"

Good nitpicking, John. I made the bad assumption that

If !phsnum <> Prevphsnum Then
linecount = 1
End If

would set linecount = 1 for the first entry. It would have, but
unfortunately the first value of !phsnum was 0, which is what Prevphsnum
gets assigned as its default value.

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

"John Spencer" <spencer@chpdm.edu> wrote in message
news:enotpTFKIHA.536@TK2MSFTNGP06.phx.gbl...
> Glad it is working.
>
> Douglas did all the work. I just nit-picked.
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "nomadk" <nomadk@discussions.microsoft.com> wrote in message
> news:52BCA72F-C37E-4F55-8F1A-4A7EFD8A710A@microsoft.com...
>> Perfect!
>>
>> Thanks John and Doug for setting me straight.
>>
>> "John Spencer" wrote:
>>
>>> Yes. On the first record phsNum = 0 and Prevphsnum = 0 so the LineCount
>>> does not get set to 1 and remains at the default value of 0.
>>>
>>> You can fix that by setting LineCount to 1 before you enter the loop.
>>>
>>> Dim LineCount as Long
>>> LineCount =1 'Set default value
>>>
>>> --
>>> John Spencer
>>> Access MVP 2002-2005, 2007
>>> Center for Health Program Development and Management
>>> University of Maryland Baltimore County
>>> ..
>>>
>>> "nomadk" <nomadk@discussions.microsoft.com> wrote in message
>>> news:EAFC820C-85D0-4EEA-8998-68A2A22C68E9@microsoft.com...
>>> > Thanks John, I should have noticed that myself.
>>> >
>>> > I don't want to be a pest, but now I get:
>>> >
>>> > phsnum Match linnum
>>> > 0 1100 0
>>> > 0 2250 1
>>> > 1 1100 1
>>> > 1 1140 2
>>> > 1 1230 3
>>> > 3 1290 1
>>> > 4 2280 1
>>> >
>>> > Can you tell me why the first record starts at "0", rather than "1"?
>>>
>>>
>>>
>
>

==============================================================================
TOPIC: db.execute strSql
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e03101b401433651?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 5:10 am
From: "John Spencer"

I would use an SQL statement like the following and loop through the table
names assinging them to strTableA and strTableB.

I would build a table with two fields and 26 records containing the
tableNames.

Then you could execute code something like the following UNTESTED AIR CODE

Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim strTableA as String, strTableB as String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset ("SELECT * FROM tableListTables")

While rstAny.EOF = False
strTableA = rstAny!ASTUTable
strTableB = rstAny!SCASTable

strSql = "UPDATE [" & strTableA & "] as A INNER JOIN [" & strTableB "] as
S" & _
" ON A..[STULINK]=S.STULINK] " & _
" SET A.PRIMDISABI = """" " & _
" WHERE [S].[PRIMDISABI] Is Not Null And [A].[STATUS]) Is Null"
dbany.execute strSQL, dbFailOnError

rstAny.MoveNext

Wend

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"Siew-Ming" <SiewMing@discussions.microsoft.com> wrote in message
news:4EEAACF5-5392-4632-92F6-F91C083FE6BE@microsoft.com...
> Hi,
>
> What's the best way to execute a query 26 times for different file names?
>
> strSql = "UPDATE ASTU7001 INNER JOIN SCAS7001 ON
> [ASTU7001].[STULINK]=[SCAS7001].[STULINK] SET SCAS7001.PRIMDISABI = ""
> WHERE
> ((([SCAS7001].[PRIMDISABI]) Is Not Null) And (([ASTU7001].[STATUS]) Is
> Null));"
> db.Execute strSQL, dbFailOnError
> MsgBox db.RecordsAffected & " record(s) were updated."
>
> * ASTU7001 and SCAS7002 can be replaced with ASTU7002 and SCAS7002 or
> ASTU6001 and SCAS6001.
>
>
> Thanks for you input in advance,
> Sming

==============================================================================
TOPIC: PDF Loop
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/cfa74507feea73f5?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 5:16 am
From: "dhoover via AccessMonster.com"

I'm using 6.0

Daniel wrote:
>What version of Acrobat do you have? (different version require different
>coding).
>
>Also, if you google you'll find examples. That is where I managed to find
>the info to put my code together.
>> I have a form with a dropdown box (cboFindCustomer). When a button is
>> clicked on the form, a report (Customer instructions) is opened and filtered
>[quoted text clipped - 5 lines]
>> Can someone please help me with the code to print the opened file to a pdf.
>> thanks!

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

==============================================================================
TOPIC: linking?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/831cc4d1b7d2c2f3?hl=en
==============================================================================

== 1 of 2 ==
Date: Fri, Nov 16 2007 6:05 am
From: ksawb

I have one table. I want to pick from field one (Control Box) and have field
two show up in a (text box) I think this should be easy but a month of
trying to figure this I'm about to tell the boss to &^*((^&

HELP??

== 2 of 2 ==
Date: Fri, Nov 16 2007 7:56 am
From: "John Spencer"

BY control box, do you mean a dropdown combobox?

If so, you can include both fields in the underlying source for the combobox
and in the text control set the control value to
=NameOfCombobox.Column(1)

Column numbers start from zero, so column one would be the second column in
the combobox's row source. If you don't want to see the second field when
you are choosing from the combobox, just set the column widths property of
the combobox to ;0. That will automatically show the first column and hide
the second column.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"ksawb" <ksawb@discussions.microsoft.com> wrote in message
news:4C386B3E-1C1A-4BEA-9961-FAFD8E9478B1@microsoft.com...
>I have one table. I want to pick from field one (Control Box) and have
>field
> two show up in a (text box) I think this should be easy but a month of
> trying to figure this I'm about to tell the boss to &^*((^&
>
> HELP??

==============================================================================
TOPIC: exif data from jpg's
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4b577c7fec3e2b7e?hl=en
==============================================================================

== 1 of 4 ==
Date: Fri, Nov 16 2007 6:18 am
From: CowDev

Can someone please enlighten me on how to retrieve exif data from jpg's using
vba? My app reads job photos and their path from a specified folder and adds
the file and path data to a photos table where the user can add captions etc.

The app also creates a copy of each photo much smaller in size yet suitable
for documentation, printing, and the transfer of the job file to other users.

I need to get at the "Date Picture Taken" property since the file creation
date is useless once the new, lower-res photo is created.

I have searched and searched for answers to my dilema with not much luck. I
understand that installing the new Windows Desktop Search utility would make
the exif data accessible however, I will not force my users to install it.

Thanks in advance.

== 2 of 4 ==
Date: Fri, Nov 16 2007 7:18 am
From: "Albert D. Kallal"

There is some sample VB6 code here that should work just fine in access

http://www.vbaccelerator.com/home/VB/Code/vbMedia/Using_GDI_Plus/Reading_EXIF_and_Other_Image_Properties/article.asp

I assume you have some code to already read the list of files in a
directory?

Here is some code that will traverse a directory structure, and give you a
"collection" of files;

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub

Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub

And, for prompting to open a file bowerse dialog:
file open dialog:
http://www.mvps.org/access/api/api0001.htm

browse to folder open dialog:
http://www.mvps.org/access/api/api0001.htm

Between all of the above links, you should be able cobble together some code
in which clicking a button can browses to a folder, then reads in the list
of file names, and then reads the tags (exif) info into a database if you so
need.......

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 3 of 4 ==
Date: Fri, Nov 16 2007 7:47 am
From: "Albert D. Kallal"

Here one that looks to be native vb6 code......

even better for ms-access.....

http://sourceforge.net/projects/exifclass/

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 4 of 4 ==
Date: Fri, Nov 16 2007 9:57 am
From: CowDev

Thanks for the quick reply Albert. I did come across that sample code in my
searches. However, I am unfamiliar with incorporating vb6 .cls files into MS
Access (2003) and have not found any tutorials for doing this. Can you
assist.

jim

==============================================================================
TOPIC: no option selected
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7b63dc9497a0b74c?hl=en
==============================================================================

== 1 of 2 ==
Date: Fri, Nov 16 2007 6:55 am
From: MKM

:o( As crazy as it sounded, I have noticed that Access responds differently
to keystrokes and mouse clicks, so I really thought it would work! But, no
go. I tried doing that then save/close with keystrokes only; I tried
save/switch views...no go.

"tina" wrote:

> open the form in Design view, click on the option group "frame" to select
> it. in the Properties box, go to the Default line. type a number 1 in the
> line, and press the Down arrow on the keyboard once. press the Up arrow
> once; the number 1 should be highlighted - DON'T touch it with the mouse.
> press the Delete key on the keyboard to delete the 1 value on the Default
> property line. press the Down arrow on the keyboard once. save the form and
> close it.
>
> hth
>
>
> "MKM" <MKM@discussions.microsoft.com> wrote in message
> news:ED263981-B6BD-47F9-AF89-83B9A6FE7682@microsoft.com...
> > I should have mentioned...I already tried setting the Default Value of the
> > frame to Null (literally typed in) and I also tried deleting the contents
> of
> > the Default property field in the property box. I also tried setting the
> > default value to "". When the form opens it always has option 1 selected.
> > Maybe setting it in the OnOpen event?
> >
> > "tina" wrote:
> >
> > > in A2003 and prior, you would remove the Default value in the option
> control
> > > (the "frame").
> > >
> > > hth
> > >
> > >
> > > "MKM" <MKM@discussions.microsoft.com> wrote in message
> > > news:098A0A17-B622-4076-BA5B-2B18C19E5C15@microsoft.com...
> > > > I have two option btns in an option group. When the form opens I want
> > > > neither selected. What property controls that? is it a frame
> property or
> > > a
> > > > property of each btn? I'm using Access 2007, but it must be the same
> for
> > > > earlier versions...maybe!
> > >
> > >
> > >
>
>
>

== 2 of 2 ==
Date: Fri, Nov 16 2007 6:58 am
From: MKM

I even created a new one (via Wizard) and choose "No default" I got a default!

"tina" wrote:

==============================================================================
TOPIC: releasing mailing data cource
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/80e18b565603c4b1?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 6:54 am
From: Klatuu

All you need is:

' Close mailingDB
DoCmd.Quit
--
Dave Hargis, Microsoft Access MVP

"judith" wrote:

> I copy a table from my current database over to an independant database
> (mailingBaseDB) and then mailmerge this data. When I have executed the
> mailmerge I close the merge document but it leaves the database
> (mailingBaseDB). I am using Office 2000
>
> ' Set the mail merge data source
> MergeFileName.MailMerge.OpenDataSource _
> Name:=docPath & "\MailingBaseDB.mdb", _
> Connection:="TABLE mailMergeBaseGeneral", _
> SQLStatement:="SELECT * FROM [mailMergeBaseGeneral]"
>
> ' Execute the mail merge.
> Set closeFileName = MergeFileName
> MergeFileName.MailMerge.Execute
> closeFileName.Close wdDoNotSaveChanges
>
> I tried closing the database with code but that didnt seem to work
>
> ' Close mailingDB
> Set mergeInfo = OpenDatabase(docPath & "\MailingBaseDB.mdb")
> mergeInfo.Close
>
> Any suggestions please
>
>

==============================================================================
TOPIC: Assigning data to a readonly subform
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/cbc27832cea93a95?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 7:54 am
From: Tasmania

Hi. I am creating a employee computer inventory database. The tables are
Employee, Hardware, Status.
Fields of the tables are-
Employee: EmpID,LastName,FirstName etc.
Hardware: HID, Make, Model, AsseTag, Category, Serial#, AcquiredDate
PurchaseOrder#, EmpID, StatusID etc.
Status: StatusID, Status

Hardware table is already populated with a series of items. A new piece of
hardware purchase goes to the hardware table.
I am using a form to enter hardware data.
When a new Employee comes, I enter the Employee data through a form.
Then I have to assign a piece of hardware to that employee, which will open
the Hardware form, and pick a hardware item for that employee.
The Employee form will have a hardware subform that will show the specific
hardware data assigned to that employee, but this subform is for viewing only.
The hardware Assigned with the specific Serial# will belong to only that
employee.
I am not sure how I can achieve that. I am a novice programmer. I greately
appreciate any help.
--
thadi

==============================================================================
TOPIC: Time (hh:mm) when a specific fiel is changed
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/d46b238df2f5ad2f?hl=en
==============================================================================

== 1 of 2 ==
Date: Fri, Nov 16 2007 8:12 am
From: Luis Marques

It is possible to know the time (hh:mm) when a specific fiel is changed?

I need to use that "time", to do a calculation.

Thanks in advance.

Luis

== 2 of 2 ==
Date: Fri, Nov 16 2007 8:37 am
From: "Douglas J. Steele"

Only if you saved the time. Access doesn't do it on its own.

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

"Luis Marques" <LuisMarques@discussions.microsoft.com> wrote in message
news:93756FCF-9DBF-4456-B179-2962AD144D85@microsoft.com...
> It is possible to know the time (hh:mm) when a specific fiel is changed?
>
> I need to use that "time", to do a calculation.
>
> Thanks in advance.
>
> Luis

==============================================================================
TOPIC: recordset - incorrect number of records returning
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/87a043fd38669c0e?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 8:46 am
From: Steven Cheng

i have a base table (transactioncodes) which has 4 fields (transactionno
(duplicates allowed, indexed, integer type), description, default code, and
search string).

i have declared two variables, temprs and rs1 which call upon
transactioncodes twice for different reasons:
1. set rs1 = db.openrecordset("Select * from
Transactioncodes",dbOpensnapshot) used at the beginning of the module and
left until the module closes.
2. set temprs = db.openrecordset("Select * from Transactioncodes WHERE
transactionno = " & x) which is called upon to look for only those records
that satisfy the x value passed onto it (integer type). this is created in
the middle of the procedure.

i have tested the sqlstring used for temprs and it appears to be fine.
however, in certain instances where a ceran transaction code appears multiple
times, temprs should return those records but only returns it once.

is this having to do with the fact that i transactioncodes called as a
snapshot previously?

==============================================================================
TOPIC: Looping through controls
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/86ccbdf3634a1c3b?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 9:03 am
From: NasaDBGuy

is there a way I can loop through all the controls in all the subforms in an
Access form? I want to call a function in each control, so I have something
like:

Dim frmLoop As Form
Dim ctrLoop As Control

For Each frmLoop In Forms
For Each ctrLoop In frmLoop
Me.frmLoop.Controls.ctrLoop.OnUndo = "myProcedure"

Next ctrLoop
Next frmLoop

Am I on the right track? I know my syntax is bad. That's why it's not
working? Thanks for any help

==============================================================================
TOPIC: Access with Linked SQL Tables
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/969a336c09c7dfd3?hl=en
==============================================================================

== 1 of 4 ==
Date: Fri, Nov 16 2007 9:15 am
From: Franco

I was reading the article How to Migrate from Access to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx
The technical paper states.
Instead of retrieving the whole set of records using the form filter behind
the forms to get the desired result set, you should change the recordsource
property of the form to include a WHERE clause, so that the filtering will
occur on the server and the filtered set of results will be returned from SQL
Server:

The paper also states.
When a linked table is queried (through an Access query or otherwise), every
record in that table is returned from SQL Server, irrespective of any
filtering through the SQL WHERE, UNION, or JOIN clauses.

I'm somewhat confused. Does the WHERE clause actually filter the data on
the server or does it fetch all the data from the server and apply the filter
locally?

Thanks

== 2 of 4 ==
Date: Fri, Nov 16 2007 9:30 am
From: "Albert D. Kallal"

"Franco" <Franco@discussions.microsoft.com> wrote in message
news:5C0C82DD-9EB0-4AC4-8763-130F031E2878@microsoft.com...

>I was reading the article How to Migrate from Access to SQL Server 2000
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx

> The technical paper states.
> Instead of retrieving the whole set of records using the form filter
> behind
> the forms to get the desired result set, you should change the
> recordsource
> property of the form to include a WHERE clause, so that the filtering will
> occur on the server and the filtered set of results will be returned from
> SQL
> Server:

Not only should you do the above for sql applications, but also for jet
based applications.

>
> The paper also states.
> When a linked table is queried (through an Access query or otherwise),
> every
> record in that table is returned from SQL Server, irrespective of any
> filtering through the SQL WHERE, UNION, or JOIN clauses.

**** READ **** the paragraph heading:

**** Using Views for Queries with Complex Joins ****
The above text you are quoting is talking about complex joins. Without
question, jet will pull the whole table when you do complex joins. So, the
SIMPLY solution stated in that paragraph is to simply create a view on the
sql server side (another possible solution is to use pass-through query).

So, to be clear:

ms-access when used with a jet back end (mdb file). DOES NOT PULL THE WHOLE
TABLE over the network

ms-success when used with a sql server back end (sq server) DOES NOT PULL
THE WHOLE TABLE over the network.

When you do COMPLEX JOINS, jet will often screw up the query..and does pull
both of the tables over the network. So, as the paragraph above title
stats -- "use views" for queries with complex joins"

Yes, is *very* good advice, and it is correct.

However, when not doing complex joins, linked tables *can* be used without a
view, and often ms-access will not pull the whole tables. However, you are
VERY much more safe to use a view, and link to that view (they perform
*VERY* well,a nd also work well with "where" clauses when opening a form.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 3 of 4 ==
Date: Fri, Nov 16 2007 9:47 am
From: "Albert D. Kallal"

To further clarify:

> However, when not doing complex joins

In the above, I don't mean not doing joins at all, I mean a simple normal
joins between two tables.

however, since JET linked tables to sql server *often* gets messed up, if
you experience a performance problem with joined linked tables, then I
suggest you use a view.

Joins on linked tables often don't perform well. (and, with complex
joins...it even worse..and tables will be pulled over the wire).

I also find the same occurs for combo boxes on forms. If you go:

select id, Firstname, Lastname, company from tblCustomers.

If the above is the *direct* setting for the combo box, the above sql *then*
has to look at the linked table....get the legal fields....check the table
link...and *then* pull the 4 columns of data.

*anytime* you resolve *any* local sql that has to resolve to a *linked*
table which in tern resolves to sql server...you find a delay occurs. You
can improve the response time by replacing the above combo box with a view
that retunes ONLY those above 4 columns (and, has your correct sorting
etc.). Then, create a link to that view, and then use the view name for the
combo box source

note:

O N L Y use the view name, DO NOT use ANY sql at all!!

eg:

nameOfView

If you use nothing more for the combo box then *just* the linked view name,
you find it runs better then using sql that resolves to a link table. You
will also find things snappy if you use just the table name..but, then you
can't control what columns are returned.

This trick will increase your load time by a lot on forms with several combo
boxes. However, if you have more then 4, or 5 combo boxes on the form, then
you often will notice some delays in the form load. (if you have a lot of
combo boxes, then hopefully some can be based on local tables...especially
if they are static. This is not a huge deal, but if you have many combo
boxes on a form, using views and removes the source sql for the combo boxes
will increase load times by a lot (in fact, it speeds things up enough to
avoid you having to write more code, and using more drastic measures to
speed up form loading time).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 4 of 4 ==
Date: Fri, Nov 16 2007 10:00 am
From: Franco

Thank you for the excellent response! This definately clarified the issue
for me.

"Albert D. Kallal" wrote:

>
> To further clarify:
>
> > However, when not doing complex joins
>
> In the above, I don't mean not doing joins at all, I mean a simple normal
> joins between two tables.
>
> however, since JET linked tables to sql server *often* gets messed up, if
> you experience a performance problem with joined linked tables, then I
> suggest you use a view.
>
> Joins on linked tables often don't perform well. (and, with complex
> joins...it even worse..and tables will be pulled over the wire).
>
> I also find the same occurs for combo boxes on forms. If you go:
>
>
> select id, Firstname, Lastname, company from tblCustomers.
>
>
> If the above is the *direct* setting for the combo box, the above sql *then*
> has to look at the linked table....get the legal fields....check the table
> link...and *then* pull the 4 columns of data.
>
> *anytime* you resolve *any* local sql that has to resolve to a *linked*
> table which in tern resolves to sql server...you find a delay occurs. You
> can improve the response time by replacing the above combo box with a view
> that retunes ONLY those above 4 columns (and, has your correct sorting
> etc.). Then, create a link to that view, and then use the view name for the
> combo box source
>
> note:
>
> O N L Y use the view name, DO NOT use ANY sql at all!!
>
> eg:
>
> nameOfView
>
>
> If you use nothing more for the combo box then *just* the linked view name,
> you find it runs better then using sql that resolves to a link table. You
> will also find things snappy if you use just the table name..but, then you
> can't control what columns are returned.
>
> This trick will increase your load time by a lot on forms with several combo
> boxes. However, if you have more then 4, or 5 combo boxes on the form, then
> you often will notice some delays in the form load. (if you have a lot of
> combo boxes, then hopefully some can be based on local tables...especially
> if they are static. This is not a huge deal, but if you have many combo
> boxes on a form, using views and removes the source sql for the combo boxes
> will increase load times by a lot (in fact, it speeds things up enough to
> avoid you having to write more code, and using more drastic measures to
> speed up form loading time).
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
>
>
>
>
>

==============================================================================
TOPIC: SQL Back End - Access Front End
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/bf68c046fb543559?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 9:42 am
From: "Rick A.B."

On Nov 15, 11:52 am, TheNovice <TheNov...@discussions.microsoft.com>
wrote:
> All,
>
> I have a need/Challenge to move all of our Access Databases and convert them
> to SQL but keeping Access as the front end for all of the users.
>
> Can anyone guide me to the right direction on how to accomplish this?
>
> Thanks in advance,
> --
> -The Novice
> Learn Today, Teach Tomorrow
>
> Great Success is ones ability to ask for Help.

I would suggest downloading SQLexpress and playing with that. I made
a copy of my FE and a Copy of my BE, linked copy of FE and copy of BE
together and used the Upsizing wizzard in access. It worked
perfectly. Had to make a few changes in the code in FE after testing
but just minor changes.
Hope that helps.

Rick

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Jumat, 16 November 2007

25 new messages in 13 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya