Running SQL SELECT statements in Access runtime

[Access 2003]

I have a number of applications deployed to clients that have only Access 
runtime throughout the organization. In all cases, I have remote access to 
their systems, since I also provide IT support. All are split FE/BE 
implementations.

Occasionally, a customer will need a quick, one-time query or report, or I 
need to do a query remotely to identify a data anomaly. Since I cannot run a 
query directly in Access runtime without embedding it in a form, I have (at 
least) two choices (leaving out the option, for the moment, of deploying a 
full copy of Access to the server or remote station):

1. Download a copy of the data file to my system and run the query 
there--not a good option if the back end is large.
2. Write a form or report just for this, upload it as part of a temporary 
front-end, then run the form or report.

I then had an idea: I could add an input box to my hidden development menu 
where I could type (or paste) the SQL string manually, and then run it. 
Herein lies my question: how can I display the results? I would use a 
datasheet, but any type of form, including a datasheet, requires controls for 
each field, and short of just making a generic datasheet form with lots of 
controls and setting the ControlSource of each at runtime, I cannot think of 
any simple way to display on-the-fly query results.

I am probably missing something basic here, but I have just never given this 
much thought and figure that someone has ideas superior to those striking me 
at the moment. I guess the ideal would be a "dynamic datasheet form", wherein 
there would automatically be a control on the form for each field in my query.

If nothing else, I may have to just revert to exporting the results to an 
Excel sheet, which I can then open on the remote system with the Excel Viewer.

Other ideas?
0
Utf
1/13/2010 2:14:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
790 Views

Similar Articles

[PageSpeed] 7

Add an unbound subform viewed as Datasheet. Create code that takes the SQL 
string from the text box, binds the subform to it and then requeries the 
subform.

"Brian" <Brian@discussions.microsoft.com> wrote in message 
news:F4EB1635-024A-4802-9312-5437647623AD@microsoft.com...
> [Access 2003]
>
> I have a number of applications deployed to clients that have only Access
> runtime throughout the organization. In all cases, I have remote access to
> their systems, since I also provide IT support. All are split FE/BE
> implementations.
>
> Occasionally, a customer will need a quick, one-time query or report, or I
> need to do a query remotely to identify a data anomaly. Since I cannot run 
> a
> query directly in Access runtime without embedding it in a form, I have 
> (at
> least) two choices (leaving out the option, for the moment, of deploying a
> full copy of Access to the server or remote station):
>
> 1. Download a copy of the data file to my system and run the query
> there--not a good option if the back end is large.
> 2. Write a form or report just for this, upload it as part of a temporary
> front-end, then run the form or report.
>
> I then had an idea: I could add an input box to my hidden development menu
> where I could type (or paste) the SQL string manually, and then run it.
> Herein lies my question: how can I display the results? I would use a
> datasheet, but any type of form, including a datasheet, requires controls 
> for
> each field, and short of just making a generic datasheet form with lots of
> controls and setting the ControlSource of each at runtime, I cannot think 
> of
> any simple way to display on-the-fly query results.
>
> I am probably missing something basic here, but I have just never given 
> this
> much thought and figure that someone has ideas superior to those striking 
> me
> at the moment. I guess the ideal would be a "dynamic datasheet form", 
> wherein
> there would automatically be a control on the form for each field in my 
> query.
>
> If nothing else, I may have to just revert to exporting the results to an
> Excel sheet, which I can then open on the remote system with the Excel 
> Viewer.
>
> Other ideas? 


0
David
1/13/2010 4:33:32 AM
Just a thought, add a shortcut key in a macro named AutoKeys that opens your 
form or hidden development menu. Then you do not need a temporary application.

Luck
Jonathan

"David C. Holley" wrote:

> Add an unbound subform viewed as Datasheet. Create code that takes the SQL 
> string from the text box, binds the subform to it and then requeries the 
> subform.
> 
> "Brian" <Brian@discussions.microsoft.com> wrote in message 
> news:F4EB1635-024A-4802-9312-5437647623AD@microsoft.com...
> > [Access 2003]
> >
> > I have a number of applications deployed to clients that have only Access
> > runtime throughout the organization. In all cases, I have remote access to
> > their systems, since I also provide IT support. All are split FE/BE
> > implementations.
> >
> > Occasionally, a customer will need a quick, one-time query or report, or I
> > need to do a query remotely to identify a data anomaly. Since I cannot run 
> > a
> > query directly in Access runtime without embedding it in a form, I have 
> > (at
> > least) two choices (leaving out the option, for the moment, of deploying a
> > full copy of Access to the server or remote station):
> >
> > 1. Download a copy of the data file to my system and run the query
> > there--not a good option if the back end is large.
> > 2. Write a form or report just for this, upload it as part of a temporary
> > front-end, then run the form or report.
> >
> > I then had an idea: I could add an input box to my hidden development menu
> > where I could type (or paste) the SQL string manually, and then run it.
> > Herein lies my question: how can I display the results? I would use a
> > datasheet, but any type of form, including a datasheet, requires controls 
> > for
> > each field, and short of just making a generic datasheet form with lots of
> > controls and setting the ControlSource of each at runtime, I cannot think 
> > of
> > any simple way to display on-the-fly query results.
> >
> > I am probably missing something basic here, but I have just never given 
> > this
> > much thought and figure that someone has ideas superior to those striking 
> > me
> > at the moment. I guess the ideal would be a "dynamic datasheet form", 
> > wherein
> > there would automatically be a control on the form for each field in my 
> > query.
> >
> > If nothing else, I may have to just revert to exporting the results to an
> > Excel sheet, which I can then open on the remote system with the Excel 
> > Viewer.
> >
> > Other ideas? 
> 
> 
> .
> 
0
Utf
1/13/2010 11:24:02 PM
Reply:

Similar Artilces:

Need only one DLL instance to run...
Hi all, 1: if two apps load the same DLL - LoadLibrary(...) - system will create to different instance of the DLL... Now, in my DLL i've a CList and i need it to be visible to all instance and all apps!!!! Is there a way!? 2: I need the dll to remain loaded till machine reboot!!! Is it possible!? Thanks Ale >if two apps load the same DLL - LoadLibrary(...) - system will create to >different instance of the DLL... >Now, in my DLL i've a CList and i need it to be visible to all instance and >all apps!!!! > >Is there a way!? It'll be difficult to share a ...

How do you escape from Access Calendar.
Hi, I using Access' active x calendar control in Access within XP Office pro. The code is working fine, but how do I escape from the calendar if I accidently click on my date field? My code is setup to dispaly the calendar control when I click on the date field. When I click on a date on the calendar, I get the date, assign it to my field, make the calendar invisible, and set focus on my date field. My question is, how do I exist the calendar if I click on the date field by mistake. I tryed to check for the Escape key in the KeyDown event for the Calendar, the KeyDo...

credentials to run this report are not stored
Hi all, I'm getting this problem when I try and create a timed subscription. Error: ...credentials to run this report are not stored..... ok I created another folder under the same folder where I'm having this problem. loaded the report and I have no problem. I even moved the current folder with that report in it. Creted another folder with the same name and still the same problem with that named folder. With a different name for the folder I have no problem??? I'm stuck here.. almost all post just talk about storing the credentials, Already done and works...

Importing Statements #4
I currently download statements from my bank to Money 2007 as my bank no longer supports direct downloading. The problem is that I have multiple accounts and a few accounts are downloading to the wroing acocunt in money. How can I change the account that the downloaded data goes to? In microsoft.public.money, dsirjuesingh wrote: >I currently download statements from my bank to Money 2007 as my bank no >longer supports direct downloading. >The problem is that I have multiple accounts and a few accounts are >downloading to the wroing acocunt in money. >How can I change the a...

Set Textbox value by selecting item from combobox but it must be editable
Hi, >I have one projrct in MS Access. >I have one form in which there is one Combobx(PalletNo_combo) and one Textbox(ShippedQuantity). >Now when I select an item from Combobox then it will automatically set the value of Textbox. >For that I am using code in control source properties of Textbox =(DLookUp("val([QTYONHAND])","PRODUCTION","PalletNo=[Forms]! [ShippingEntry]![PalletNo_Combo]")) >Uptil here its fine. >But after that, in some scenario,I have to change the value of that Textbox. >But when I click on the Textbox and enter something ...

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

Repost: Error running Report in an Access 2003 db from Access 2007
Ok, clarification - ignore the code from my original post, some of the reports do work. The ones that don't are reports that I have being filtered. Here is the code from one of those buttons: Private Sub Ok_Click() On Error GoTo Ok_Click_Err 'using the customer sub form for customer state report to filter the report, clicking ok will open report for selected state Dim stDocName As String Dim stLinkCriteria As String If Not IsNull(Me.Search_Results) Then stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"...

Automatically run macro
My name is Mike and i have a question about microsoft excel macro's. Attached is a copy of the excel sheet im working on. Below the excel sheet is the macro I built. Some of the cells contain given values and some cells are calculated from formulas. Cell (G4) is my given value...it is related to cell (C32). The point is, I plug a value into cell (C10) and it runs through the rest of the calcs in the other cells and gives me a value to cell (C32). I built a macro that works as a goal seek pretty much. The macro makes cell (C32) equal to cell (G4) and gives me the value for cell (C10). I wan...

New accounts cannot access their mailbox
Hi I am having a problem with new accounts in SBS 2003. When we create new users they are unable to log into there mailbox if you check there "email address" tab in ad the do not receive any email addresses. We have tried loading the latest service packs and patches and rebuilt/reload the RUS but without any success Thanks Kris In news:8A0CEDE8-B192-4278-82EA-85967166E324@microsoft.com, luckr01 <luckr01@discussions.microsoft.com> typed: > Hi I am having a problem with new accounts in SBS 2003. When we > create new users they are unable to log into there mailbox i...

Excel Continuous Running Total
I posted a message earlier and have received a partial solution. I want to keep track of how much stock prices go up or down with a running total of how much they go up or down over several days until the direction changes. For example, if price go up 10 on Mon, 20 on Tues, 30 on Thurs and down 10 on Fri I want my running total column to show a positive number of 60 and then a red number of 10 and continue adding the amount of the total of the down days until the market shows an up day. The formula I am now using total the first and second day but does not do a running total count if t...

display changing label caption on form as sub runs w/o screen flic
let's say i have this routine Sub Test label1.caption = "Starting ... " 'do events label1.caption = "Getting there ... " 'do events label1.caption = "Finished! ... " End Sub on my form, i have label1 right in the middle what happens is sometimes the message will change, and then sometimes it wont, or it will show the first one, skip the second and jump to the third etc etc etc so it is inconsistent. is there a way to make sure the label caption displays consistently, on time, wh...

Money 2002 will not run
I had been using Money 2002 for approx. 3 yrs on my home pc (Dell Dim 2100, XP Home Ed. w/SP2). Last year, it simply would not launch. No error message, no splash screen, no app opening, no process listed in Task Manager. Just.... nothing.... The only change that was made to the system since M2k2 last ran was upgrading my a/v solution from Trend Micro PC-Cillin to TM Internet Security. I have tried disabling every aspect of the Internet Security product, as well as completely un-installing the app, and then attempting to run Money, but the same thing (nothing) happens. I was considerin...

Get query from Access
Hello, trying to do a query. So I do the following: Data->get external data->New database query, then from the Choose Data source, I choose MS 97 ACCESS DATABASE*, then I select the database. This brings me to the QUERY wizard-Choose columns When I double click on the table/query I want, it doesn't bring the columns to choose. It changes the + sign to - sign but nothing else. I try a different database, and on this one it does work. So not sure why only on one database it works. Any suggestions? Would appreciate it. thanks, Juan ...

I can access any mailbox using OWA 2003
Hi, Exchange Server 2003 By using OWA I can open anyones mailbox. if I enter http://server/user1 in IE i can see user1's mailbox if I enter http://server/user2 i see user2's mailbox. what security setting must i change so that only the logged in user can see his or her own mailbox. thanks Mandy goto Exchange System Manager, servers, server_name, protocols, http, exchange virtual server. goto properties of exchange, click on acess tab, check basic authentication and intehrated windows authentication. Now, only if someone can answer the question I posted this morning. >...

Access 2007 Layout View for reports upgraded from Access 2003
I notice that in Access 2007 only those reports newly created in Access 2007 appear to have access to Layout View. In an ACCDB that had been converted from an Access 2003 MDB the reports that had originally been created in Access 2003 do not have access to this feature. Any attempt to do so is met with a "Layout view is unavailable for this Report." message. Is there any way, short of recreating the report and manually copying over its controls and code, to upgrade such reports so that Layout View is accessible? Hi Benjamin, 1. Open the report in design view 2. Open the property s...

Re: Workflow just wont run automatically, i have to run them manually
Yes, but i realized what i was doing wrong. I assumed [bad idea] that if i create a case and hit Save & Close the first time, taht the rule will run. In order for the rule to run automatically, it has to be Save, once it saves it, then Save & Close. Thanks for your reply. "Hi, Did you check the workflow monitor to see if the rules get triggered correctly and complete sucessfully ? Have a nice day, St=E9phane Dorrekens " --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://...

Outlook Still Running
I am running XP Pro with Office 2003 Pro and sometimes when I exit out of Outlook, Outlook.exe and Winword.exe stay as processes still running. Does anyone else have this problem and what can do to make sure this does not happen? Thanks for your help -- Neil Remove ABCD from Email address to reply <neil154ABCD@earthlink.net> wrote: > I am running XP Pro with Office 2003 Pro and sometimes when I exit > out of Outlook, Outlook.exe and Winword.exe stay as processes still > running. Does anyone else have this problem and what can do to make > sure this does not happ...

Select Names: Contacts
When I send a file to mail recipient then select To: to select a contact in my address book the cursor shows the hour glass for up to 1 min before I can select anyone Anyone else having this problem? I thought it also did it when I selected New email as well but hasn't done it yet, but no doubt it will after I post this message Cheers Version of Outlook? Type of mail account? Can you reproduce this in Outlook Safe Mode? Start-> Run; outlook.exe /safe (note the space in the command) Does it work when you recreate your mail profile? http://www.howto-outlook....

Very strange problems while running Great Plains on workstations
I notice that a few workstations in an office I support are having problems when they run Great Plains. Excel, Outlook, Word and Dynamics.exe are showing in the Application event log as being Hanging or Faulting. I also see Fault Bucket errors, but when I search online I cannot find any information online. Here is one of the Fault Bucket errors: 3:15:35 pm 28-Sep-06 Application Hang None 1001 N/A Fault bucket 296734104. Also, these workstations are experiencing problems printing PDF files. Has anyone out there seen this behavior and if so, how can these problems be fixed? Thank you, ...

Publisher 2000 will not run
I had problems with Publisher 98 not running which we=20 never did solve so I installed office 2000 to see if=20 Publisher 2000 would function. Same problem: The flash=20 screen pops up and then disappears. No program. Microsoft=AE Publisher 2000 Version 6.0 has encountered a=20 problem and needs to close. We are sorry for the=20 inconvenience. Howard, hi again, Have you tried opening Publisher in safe mode? Publisher retains all printer information within its publications. If you can open Publisher in safe mode, either regress or update your printer and video drivers. -- Mary Sauer MS MV...

Macro automatic run at open
Is there a simple way to have a macro automatically started when a workbook is open? Thanks Francesco Francesco, use the workbook open event, like this, Private Sub Workbook_Open() MsgBox "It Works!" End Sub To put in this macro, from your workbook right-click the workbook's icon and pick View Code. This icon is at the top-left of the spreadsheet this will open the VBA editor to the thisworkbook module, then, paste the code in the window that opens on the right hand side, press Alt and Q to close this window and go back to your workbook, now this will run every time you ...

Microsoft Access 2003
I need to change my mailing address on the invoices I mail to customers. I have already created the 2008 file with the old address info. Can I still change my address and how? On Sat, 22 Mar 2008 07:42:00 -0700, teeny73 <teeny73@discussions.microsoft.com> wrote: >I need to change my mailing address on the invoices I mail to customers. I >have already created the 2008 file with the old address info. Can I still >change my address and how? Ummm... by opening the table containing the address and editing it; or if it's hardcoded as a label in the report, editing it ...

Running diferent query with one command
I would like to run queries with just one botton and a date dispalyed in a form. If Sunday March 09, 2008, is dispalyed I would like to click on a button and run a query that will select emloyees that are scheduled to work on this day of the week along with other pertinent information already preselected by that query. Currenlty I am using 7 diferrent buttons to run 7 differnrent append queries but it is too confusing and I am sure there is an better way. Thanks Charles -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200803/1 Why 7 butt...

Win 7 Pro running 64 bit with Outlook 2007. Problem: frequent cras
I have a brand-new computer with i-7 processors and 8 GB of RAM running Win 7 Pro 64-bit. At the time of upgrading to new computer, i upgraded office 2003 to 2007. For the first month, all was well. Suddenly, my computer started crashing regularly, as in every 30 minutes. The screen would go blank, the mouse and keyboard wouldn't work, and I would have to do a hard reboot. The store where i bought the PC has done everything imaginable to try to isolate the problem. They have swapped out every component, including the RAM, motherboard, power supply, video card and all ca...

Print selections from a list
Is there a command or menu that lets me print selected rows from a long list ?? Any help will be appreciated -- gmoexcel ------------------------------------------------------------------------ gmoexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23324 View this thread: http://www.excelforum.com/showthread.php?threadid=491508 You could Autofilter just the rows you want if you have a key to filter on. If no key for filtering you may have to manually select the rows to not print and hide them. Gord Dibben Excel MVP On Wed, 7 Dec 2005 10:43:58 -0600, g...