Jumat, 01 Agustus 2014

[AccessDevelopers] Issue with Pivot Table form based on pass-through query

 

Hi all,


I'm interested if anyone might shed some light on an issue that has perplexed me for the past 2 days. My company has Access 2007.


I created a SQL pass-through query in an accdb file with an ODBC connection string embedded in the properties, and then created a pivot table form based on that query. I added the 5 fields to the pivot table, formatted a few things, and was done.


In testing, I found that when I open the form the first time, it displays normally. But if I subsequently click the Refresh button on the ribbon, the query appears to run (status bar messages), but the fields that were placed on the form disappear. This requires adding the fields to the form again to display the data after every Refresh event. This isn't an issue except it's built for Access novices.


Anyone familiar with any limitations on pass-throughs, or perhaps the order of events that might not be what I expect?


Thanks

Brian


__._,_.___
Posted by: bribielick@yahoo.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)
Yahoo Groups
New Feature! Add Polls to Conversations
You can now include a poll directly within your message. Members can view, vote and add poll options either via email or on the group. Try it today!
Did You Know?
Learn more about how to search within your groups.

Please zip all files prior to uploading to Files section.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Best option for handling data from Microsoft 365's Azure SQL

 

I am moving my education company’s database to a Microsoft 365 backend with a Access frontend at each of our six locations.  (Until now I’ve used a VPN with the backend on a company server, but this was slow and unreliable.)  It is working well so far, but there are performance issues, as I believe Crystal has noted in the past on this forum.  Building all of my queries on the Azure SQL backend has helped significantly, however.

 

I am creating a routine to make invoices for our approximately 600 students, but I believe it will be too complicated to run smoothly via the Azure SQL backend so am thinking I will need to bring the data to the local frontend, manipulate it there, and then append entries to the invoices table on the Azure SQL backend when finished.  I’m not sure what would be the most efficient way to do this, however.  The two thoughts I’ve had are to create (a) table(s) on the local frontend that are then deleted when the process is complete or to create and manipulate a class module.  I’ve been doing a lot of reading on class modules, but have never used them before.  I would appreciate any advice or ideas on what the most efficient way to handle this would be.  Thank you for all your help and consideration.

 

Ryan Hagglund

MY English School

Yamagata, Japan

 

 

 

__._,_.___
Posted by: "Ryan Hagglund" <rhagglund@myeigo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)
Yahoo Groups
New Feature! Add Polls to Conversations
You can now include a poll directly within your message. Members can view, vote and add poll options either via email or on the group. Try it today!

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] corrupted database

 

Bill:

Sometimes they get so damaged that "Compact and Repair" can't fix?

Gary

At 09:38 AM 8/1/2014, you wrote:

Bill-

If you�re losing indexes every time you compact / repair, I would strongly recommend building a new database and importing all the objects from the old one.  There�s something broken in those tables that might be fixed by doing that.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)


--------------------------------------------------------
Gary D. Schwartz          Please reply to: gary.schwartz@pobox.com

__._,_.___
Posted by: "Gary D. Schwartz" <gary.schwartz@pobox.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (6)
Yahoo Groups
New Feature! Add Polls to Conversations
You can now include a poll directly within your message. Members can view, vote and add poll options either via email or on the group. Try it today!

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] corrupted database

 

Bill-


If you're losing indexes every time you compact / repair, I would strongly recommend building a new database and importing all the objects from the old one.  There's something broken in those tables that might be fixed by doing that.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Aug 1, 2014, at 8:30 AM, 'Gary D. Schwartz' gary.schwartz@pobox.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

When I built the form, I recall a lot of cut-n-paste, then going back and changing the formula in each one.

As for your new question, I do not remember ever seeing that.

A little more detail on version setup and the specific problem would be helpful in diagnosing, and perhaps someone else here will have more experience with this problem.

Gary


At 11:43 AM 7/31/2014, you wrote:

Gary,
That does help.  It will take some time to build the form but I will get started.
 
Also, one more question.  When the database does a compact and repair I seem to loose primary keys and relationships in about 5 tables, always the same tables.  I have to go in and fix them and then it all works well again.   Any Idea why that would happen?
 
Bill
 
MN
 
From: MS_Access_Professionals@yahoogroups.com [ mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 30, 2014 1:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] corrupted database
 
I had a similar issue on a multi-table database, where I need to verify
before/after maintenance or backup work.

I built a form where I have a field for each table I want to review.

Inside that field, I built an event using this code:

=DCount("[indexfield]","[tablename]")

An index field is used as there is always a value in every row. I believe
that
it only counts rows where that field is occupied.

I monitor about 40 different tables this way, and print out the form before
and
after. I added a field that di



--------------------------------------------------------
Gary D. Schwartz          Please reply to: gary.schwartz@pobox.com

__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (5)
Yahoo Groups
New Feature! Add Polls to Conversations
You can now include a poll directly within your message. Members can view, vote and add poll options either via email or on the group. Try it today!

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] corrupted database

 

When I built the form, I recall a lot of cut-n-paste, then going back and changing the formula in each one.

As for your new question, I do not remember ever seeing that.

A little more detail on version setup and the specific problem would be helpful in diagnosing, and perhaps someone else here will have more experience with this problem.

Gary


At 11:43 AM 7/31/2014, you wrote:

Gary,
That does help.  It will take some time to build the form but I will get started.
 
Also, one more question.  When the database does a compact and repair I seem to loose primary keys and relationships in about 5 tables, always the same tables.  I have to go in and fix them and then it all works well again.   Any Idea why that would happen?
 
Bill
 
MN
 
From: MS_Access_Professionals@yahoogroups.com [ mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 30, 2014 1:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] corrupted database
 
I had a similar issue on a multi-table database, where I need to verify
before/after maintenance or backup work.

I built a form where I have a field for each table I want to review.

Inside that field, I built an event using this code:

=DCount("[indexfield]","[tablename]")

An index field is used as there is always a value in every row. I believe
that
it only counts rows where that field is occupied.

I monitor about 40 different tables this way, and print out the form before
and
after. I added a field that di



--------------------------------------------------------
Gary D. Schwartz          Please reply to: gary.schwartz@pobox.com

__._,_.___
Posted by: "Gary D. Schwartz" <gary.schwartz@pobox.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (4)
Yahoo Groups
New Feature! Add Polls to Conversations
You can now include a poll directly within your message. Members can view, vote and add poll options either via email or on the group. Try it today!

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[belajar-access] File - Tata Tertib dan Aturan.txt

 


Tata tertib dan peraturan
Mailing List: belajar-access@yahoogroups.com

1. Mailing list ini membahas mengenai pemrograman Microsoft Access.
2. Tidak diperkenankan mem-posting topik yang tidak ada kaitannya sama sekali dengan pemrograman MS Access, peluang kerja atau tawaran kerja sama dengan keahlian di bidang MS Access, atau pengajaran/kursus MS Acces. Pelanggaran terhadap aturan ini akan di-ban dari keanggotaan milis ini.
3. Mohon berdiskusi dengan baik, dengan semangat membangun, demi kemajuan kita bersama. Hindarilah perbantahan (flame) yang bisa menjadi pertengkaran yang tidak perlu.
4 Hindari reply permintaan one-liner seperti 'saya minta juga dong', 'saya setuju', dan lain-lain yang tidak perlu.
5. Sedapat mungkin memberikan data-data yang lengkap dalam mengajukan suatu masalah untuk memudahkan rekan-rekan sesama member mengidentifikasi dan mencarikan solusi, termasuk memberikan subject yang sesuai dengan isi email, tidak dengan kata-kata seperti "tologing dong", "pusing...", "ada yang bisa bantu..", dll.

Moderator

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (89)
Did You Know?
Learn all about Conversations within groups
SPAM IS PROHIBITED

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[AccessDevelopers] File - Monthly_Notices.txt

 


Monthly notices:

Hi Kids!:

Don't forget to check out our "Links" section at the website for helpful sites. Also take a peek at books that others have found worthwhile in our books database under the 'Database' link of the main AccessDevelopers page. Feel free to add any books or links that you have found useful.

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (121)
Did You Know?
Learn all about Conversations within groups

Please zip all files prior to uploading to Files section.

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)