f



Updating MS Access tables from a query

I created a form which display data from a query, the query displays
field values from a few tables, For example in the main table I have
school ID, and school name and schooltypeID. The form displays School
name and School Type (this value come from a different table depending
on SchoolTypeID).

1, How can I use the same form to create a new record
2, How can I create a "dialog" list based on SchoolTypeID which
displays the SchoolType correctly but updates the record with the
correct type ID.


Thanks

0
effendi (72)
3/8/2006 4:07:49 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

0 Replies
647 Views

Similar Articles

[PageSpeed] 40

Reply:

Similar Artilces:

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but...

Updating MS-Access Database query
hello i need to update my MS-Access database thru matlab. when using the following code using function "updates" it updates the database i.e .......whereClause = 'where month = ''Nov'''..... Here "Nov" is an entry in the database. Now when i assign a variable to the months..ie variable name "MNTH" could contain Jan /Feb etc,.....and i want to pass the variable "MNTH" ie .......whereClause = 'where month = ''MNTH'''..... it DOES NOT update the database and i receive the error .....error in horxcat......

Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in front of the function name. What else do I need to do to get my ASP code to run this query. I'm trying to avoid reproducing MyFunction within the ASP code if possible. Thanks. Kevin On Thu, 14 Jun 2007 21:38:19 -0000, k-man <kmccrack@gmail.com> wrote: Sorry, no can do. From ASP you don't have access to the Access VBA interpreter. -Tom. >Hi: > >I have an MS Access query for a table called MyTable. One of my >fields in the query is a custom field that looks like "MyField: = >MyFunction(ID)" where ID is a field in MyTable. > >I have ASP code and I can use it to read the query OK when "MyField" >is not part of the query. If I add in MyField and then run my ASP >code to try to read all the query fields, I get an error message on my >web browser which says something to the effect of "cannot find >MyFunction."...

Accessing 2 tables having same name but different databases ms-access
Hi, I have an application with MS-Access as backend and JSP/ tomcat as frontend/webserver. For getting better response time(as access is very slow compared to other RDBMS's), I have split the main DB into two DB's on different disks on my server for better performance.I have kept the table names same but changed the DB names. How do I access tables from both the DB's? This is what I have done; I made different connection to the respective DB's i.e. con and con2. When I want to access data, I am using 2 result sets for retriving data from the tables, but getting no result. Here is my code: rs2_1=stmt2_1.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' order by Req_No"); rs=stmt.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' AND Req_No> 5500 order by Req_No"); So, rs & stmt are for latest records and rs2_1 & stmt2_1 correspond the records from older DB. I think, I'm forgetting some small thing but cant get it. Please advice. What do you mean "no result"? Does "next()" return false for both rs2_1 an= d rs? If you're really trying to improve response time, you should probably be ru= nning the two queries in separate threads ... but as you acknowledge, Acces= s may not have as high performance as other RDBMSs. You would also see rel= iability adv...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting...

Update MS Access table from another table
This is probably very simple but I can't figure out a way to update one table from another table. Here is an example: ------ TABLEA ------ first_name last_name grade ------ TABLEB ------ first_name last_name grade I want to update TABLEB's "grade" column from TABLEA (join by first_name and last_name). What's wrong with following sql? UPDATE TABLEB SET GRADE = (SELECT GRADE FROM TABLEA WHERE TABLEA.LAST_NAME = TABLEB.LAST_NAME AND TABLEA.FIRST_NAME = TABLEB.FIRST_NAME) Also, one more question: How do I update TABLE's &quo...

Accessing Oracle Tables from MS Access
How difficult is this? I'm having a tough time getting a simple select to work. I am using MS Access 2002 and Windows XP. What do I have to do to make a simple select statement like this work: select id from emp I have stumbled upon many websites although none straight to the point. Any help in how to get this done, or pointing me in the right direction of a book or website, would be greatly appreciated! berj Yes, I do have the table links working. But I did want to run the select for such things as grabbing the next number off of a sequence. As in: select test.nextval fro...

MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net/////\\\\\> wrote in message news:eed9874.-1@webx.raydaftYaTP... > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree Andree Ellert wrote: > > > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=4045&objectType=file> -Hardik ...

request for explanation on access an database in ms access
This is the code that I found on the internet for accessing an ms access database: import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // set this to a MS Access DB you have on your machine String filename = "d:/java/mdbTEST.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end // now we can get the connection from the DriverManager Connection con = DriverManager.getConnection( database ,"",""); } catch (Exception e) { System.out.println("Error: " + e); } } } What I don't understand is the declaration of database. What I did was putting the database in the same dir as the sourcecode and use "jdbc:odbc:CafeJolt.mdb". But this doesn't work. And the above code does, why is that??? Actually it's not Java question. It's ODBC question. You have to have ODBC connection. Depending of Windows it's usually something like this (for my Windows 2000): Start->Sttings->Control Panel->Administartive Tools->Data Sources(ODBC)-> User DSN -> [Add]-> Driver to Microsoft Access(*.mdb) === And here, finally you can choose file name. ...

Access MS-ACCESS database on server from client????
I m currently developing a java based application and want to know how can i make client access database located on the server? "HeMan_Speaks" <Lunar20092010@gmail.com> wrote in message news:d71ce567-0434-48b8-b1d2-bb191706ab45@w8g2000prd.googlegroups.com... >I m currently developing a java based application and want to know how > can i make client access database located on the server? I think you're going to have to expand a little bit more in your question here, as is not 100% quite clear what you're trying to do. You might want to make a little bit of a distinction between MS access, the development system that allows you to write code, build forms, and build reports, and that of the database engine that you choose to use with MS access. When you build an application MS access, you then choose your database engine, that might be oracle, SQL server, or perhaps more often leave the default jet database. So when you say make a client access application, are you talking about a piece of software that you plan to install and each computer? The fact that your introducing the issue of java further complicates your question. Perhaps your question is simply you have some java code running on a server, and with to open a mdb file (an access database file). In this situation you're not really using MS access at all here (you using what is called the jet database engine to read that mdb file). For all the trouble in this type of sce...

ms-access database cannot be updated
I have a ms-access application. The front end is ms-access and the backend is SQL Server. Originallly it worked well on windows 2000 machine. After the machine is re-imaged to windows xp machine. The application cannot be updated. All the textbox or dropdownlist become readonly. There are no particular error message. The screen is just freeze when you are trying to do update. Can someone help with this issue? Thanks! ...

MS Access Database Query
Hi, first of all I have to say I'am new to MS Access, I'm used to database systems like Oracle or MySQL. I created a simple database consisting of a single table and a form to fill in new entrys and search for existing entrys. But the search function that Access generates as I created the proper button is way to simple, I need a function that enables to search in several columns. I saw a few quite simple examples where this was done by DAO, but this doesn't work with a .mdb database, does it? (Access allways replies 'Unknown user datatype' once it gets to the line: 'Dim...

Update table using data from another table
I believe the following a valid SQL statement, but MS Access returns with an error "Operation must be an updateable query". Any suggestions would be greatly appreciated! UPDATE FERCPTILoad AS FL SET FL.[Load Dist Factor] = FL.[MW]/( select FA.[Load MW] from FERCPTIArea AS FA where FA.[Area Num] = FL.[Area Num of Bus]); *** Sent via Developersdex http://www.developersdex.com *** Bo Long wrote: > I believe the following a valid SQL statement, but MS Access returns > with an error "Operation must be an updateable query". > > Any suggestions would be greatly appreciated! > > UPDATE FERCPTILoad AS FL > SET FL.[Load Dist Factor] = FL.[MW]/( > select FA.[Load MW] > from FERCPTIArea AS FA > where FA.[Area Num] = FL.[Area Num of Bus]); > > > > *** Sent via Developersdex http://www.developersdex.com *** Why not try using DLOOKUP instead of the subselect? My guess is that since the subquery could conceivably return more than one record, the update query won't work. ...

MS Access VBA making multiple tables in database from a main table.
I have a database table called "tbl_personal" and struggling to VBA program a bit of code to create multiple tables in a database from the main "tbl_personal" table. In this case the 'color' column is the field I would like to look at. 1. Identify the number of unique records in column 'color' 2. For each color record that is label as red in the "tbl_personal" table, create a table called 'red' and populate with all red records in from the "tbl_personal" table. 3.Then move onto the next unique color and repeat the whole process again. 4. When all unique color tables are created in MS Access database end with a msg "batch process done" name surname age color country aa aaa 1 red UK bb bbb 10 green USA cc ccc 20 blue China dd ddd 30 red Germany ee eee 40 green France ff fff 1 blue Italy gg ggg 20 red Spain hh hhh 40 yellow Canada ii iii 34 black Italy =85 Is this doable automatically in MS Access? Many thanks. brenda Are you sure you want to do this? I would expect 3 tables vis: Table of Personel with PersonelID, Suname, FirstName, ColourID, CountryID & DateOfBirth (Age will be out of date next birthday and should be calculated), Table of Colours ColourID & Colour Table of Countries CountryID & Country. You can then use simple queries to return all the red people or people who live in UK. Am I missing something? Phil <bbcdancer@hotmail.com> wrote in message news:495c4ca6...

Best way to access a remote MS ACCESS database
What is the best way, via the internet, to access a remote Microsoft Access database? I was thinking of using VPN, but I thought that I would check here to see if there is a better way. Any suggestions? Thanks Do you mean remotely controlling an Access database, or linking tables over the internet? The first is doable, the second is a recipe for disaster. Remote Control: If your remote OS is Windows XP, you can use Remote Desktop (although only 1 person can access the PC at a time; this will change in the upcoming Service Pack for XP, but that may not help you now). Otherwise, GoToMyPC offers similar services, as does Terminal Server (a component of a Windows Server machine), PCAnywhere, et al. Linking to remote tables: You will almost certainly encounter severe problems with corruption, and your performance will almost certainly be so poor as to be unworkable. I attempted this once, with a web server in Canada and my machine in Augusta, GA. Even tiny data requests on a very well optimized data structure took waaaay too long to process ... it's simply not workable, IMO. Other alternatives would be covert the app to a web-based and place your data on a web server. Access/Jet isn't really designed for those workloads, but running a small site with minimal transactions and users can be done. Otherwsie, consider switching to a more robust data platform like MySQL, SQL Server, etc. -- Scott McDaniel CS Computer Software Visual Bas...

Accessing MS ACCESS database using Data Environment
HI, I have developped a program in Visual Basic 6.0 using MS Access database. I have created all my reports through Data Environment where I have entered the location of my database file (for e.g c:\project\app\test.mdb) in the Connection properties. I have installed my application on another PC using Package & Deployment wizard. When viewing the reports on the other PC, I have to enter everytime the new location of the database. How can I solve this problem, please? nadia wrote: > HI, > I have developped a program in Visual Basic 6.0 using MS Access database. >...

Accessing a MS Access database across two servers
Hi The technical support guys at my company have set up my system so that the server containing the MS Access database is on one server, while the pages that should access the database are on another server. When the pages and database are on the same server, I usually just connect through ODBC. The problem is that I don't know how to set up an ODBC connection to link from one server to a database in another. Anyone know what I am getting at and know of a possible solution, or a link to a site which may help. thanks in advance Brendan "Singularity" <Brendan.Collins@Sin...

How to access MS-Access metadata tables (eg MSysObjects)
Dear all, I have an MS-Access MDB-file with hundreds of tables and want to know when they were created and/or modified. The MS-Access metadata table MSySObjects includes this information, but I have not managed to read it from SAS. (CLI describe error tells me that I have no reading access) --> Does anybody have a working code example for this? I have tried the libname ACCESS and Libname ODBC variants. Regards Dietrich -- DIETRICH ALTE, Dipl.-Statistiker, Dr. rer. med. Wiss. Projektmanager "Study of Health in Pomerania (SHIP)" Institut f�r Community Medicine - SHIP/KEF EMA-Universit�t Greifswald - Medizinische Fakult�t Walther-Rathenau-Str. 48, D-17475 Greifswald, Germany URL ship.community-medicine.de Phone ++49(0)3834-867713, Fax ++49(0)3834-866684 ...

Running a ms access database with access 2003 in Vista
I have a Database that I am trying to run on a new vista computer. Up to now I was running it on a win 98 computer and using access 2003! I installed access 2003 and I can make it run and view the data but I can not save any new records. Has any one seen this problem yet? Thanks Clarence Wollman On Wed, 22 Aug 2007 13:17:13 -0700, cwoll <clarencewollman@gmail.com> wrote: Do you have the same problem with the Northwind sample application? (search your computer for northwind.mdb) -Tom. >I have a Database that I am trying to run on a new vista computer. Up >to now I was running it on a win 98 computer and using access 2003! I >installed access 2003 and I can make it run and view the data but I >can not save any new records. >Has any one seen this problem yet? > >Thanks Clarence Wollman On Aug 22, 7:29 pm, Tom van Stiphout <no.spam.tom7...@cox.net> wrote: > On Wed, 22 Aug 2007 13:17:13 -0700, cwoll <clarencewoll...@gmail.com> > wrote: > > Do you have the same problem with the Northwind sample application? > (search your computer for northwind.mdb) I don't have a problem opening and editing the northwind.mdb. Thanks Hi I found a workaround. This database has a fronted and a backed. When I link them in the linked table manager, I was going threw the network to my computer. The reason I am doing this is when any networked computer opens the shortcut to the front end of this computer the linked table manger knows...

MS Access Update Query help
I've created a form that has many option boxes to help users choose items (I need option boxes vs. combo boxes because they can choose multiple items per category). I've created an update query to change the value from �1 (system automatically generates that) to the actual value I want. In the criteria section of the append query, I put a simple if statement: IIf("-1","London",""). Regardless of the value that is found however, the query changes the �1 to the �true' condition. Can someone please help? I'm not quite clear whether your expressio...

update from using ms access query
Dear Sirs, I have the following select query and i need to update the field EmailLastReceivedDate (showing null in the select) to the values in the field MaxOfSentOn using access 2000 query OFCOURSE ONE STATMENT NOW I HAVE A LOOP THRU THE RECORDSET AND A UPDATE ACCORDINGLY SELECT Max(EmailsDatabase.SentOn) AS MaxOfSentOn, CoEmails.CoCode, CoBank.EmailLastReceivedDate FROM (EmailsDatabase INNER JOIN CoEmails ON EmailsDatabase.From = CoEmails.Email) INNER JOIN CoBank ON CoEmails.CoCode = CoBank.Code GROUP BY EmailsDatabase.From, CoEmails.CoCode, CoBank.EmailLastReceivedDate; MaxOfSentOn ...

query ms access database from the user
At college i have been given this piece of untidy code because it mixes a gui interface with the console, i prefare one or the other, but its not my code, i understand what it does. It opens the database CarDB by using the java odbc (Open Database Connectivity ) driver and searches for the row make with nissan and displays to the screen as text in a gui text box the columns Registration, Model, Year, Price in the same row as the nissan . I would like the programme to ask the user which car their want to search for . Unfortunately i do not learn at college or have taught myself sql. If i am understanding proberly the line ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 where Make='nissan'"); is where the query starts, but its hardcoded in, i presume i need to have a line similar to ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 request Make=' '") or something similar, am i along the correct lines? here is the code.. import java.sql.*; //import javax.swing.JOptionPane; //import javax.swing.JTextArea; import java.io.*; public class Car2 { public static void main(String args[]){ Connection connection; Statement st; String out=""; JTextArea display=new JTextArea(); try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection= DriverManager.getConnection("jdbc:odbc:CarDB","","");...

Re: How to access MS-Access metadata tables (eg MSysObjects)
The answer is given here: Problem Note 7495: No read permission on System Tables when using SAS/ACCESS Interface to ODBC with a Microsoft Access ODBC Driver http://support.sas.com/kb/7/495.html Regards Dietrich Dietrich Alte schrieb: > Dear all, > > I have an MS-Access MDB-file with hundreds of tables and want to know > when they were created and/or modified. > The MS-Access metadata table MSySObjects includes this information, > but I have not managed to read it from SAS. > (CLI describe error tells me that I have no reading access) > --> Does anybody have a working code example for this? > I have tried the libname ACCESS and Libname ODBC variants. > > Regards > > Dietrich > -- DIETRICH ALTE, Dipl.-Statistiker, Dr. rer. med. Wiss. Projektmanager "Study of Health in Pomerania (SHIP)" Institut f�r Community Medicine - SHIP/KEF EMA-Universit�t Greifswald - Medizinische Fakult�t Walther-Rathenau-Str. 48, D-17475 Greifswald, Germany URL ship.community-medicine.de Phone ++49(0)3834-867713, Fax ++49(0)3834-866684 ...

Re: How to access automatically in SAS a MS Access table name
i am not sure if SAS will support long file name if you use libname. a workaround is to use passthrough. On Jan 16, 2008 12:31 PM, JKamgang@Yahoo.com <JBKamgang@gmail.com> wrote: > Dear All, > > > > Happy New Year , > > > > Please, I need your help in figuring out how to access in SAS a MS > Access > table name which has more than 32 characters. As this program merges 5 > databases, I need to have almost everything automated. That's why I > don't want to rename manually the table names. > > > > Please find the program below. > > > > Thanks in advance for your kind assistance, > > > > Jean Baptiste > > > > The program: > > - - - > > > > options VALIDVARNAME=ANY ; > > libname DBsource access 'S:\Data > Management\External\Projects\MTCT-Plus\MTCT-Plus November 2007 > Data\Programs\Data\MTCT-PlusData All.mdb'; > > /* Creating the Pediatric HIV result information additional */ > > > > data* pedResultadd ; > > set dbsource.'tblInfantHIVTestResultsAdditional'n (keep = > > testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text > > ); > * > > run*; > > - - - The log - - > > 1636 > > 1637 /* Creating the Pediatric HIV result information additional */ > > 1638 > > 1639 data pedResultadd ; > > 1640 set dbsource.'tblInfantHIVTestResultsAdditional...

Web resources about - Updating MS Access tables from a query - comp.databases.ms-access

Wikipedia:Updating information - Wikipedia, the free encyclopedia
This page documents an English Wikipedia editing guideline . It is a generally accepted standard that editors should attempt to follow, though ...

Facebook Updating Software Development Kit For Apple IOS 6 To Reflect Integration
With Facebook integration officially coming to Apple’s iOS 6 mobile operating system for its iPhone and iPad tablet, what new features are in ...

Updating how Cost per Click is Measured on Facebook
To help advertisers better understand how their ads perform, we're updating the definition of cost per click on Facebook. Learn what the change ...

Updating Your App for iOS 9: What You Need to Know
Updating Your App for iOS 9: What You Need to Know

Facebook for iPhone 3.0 to Include Page Updating
... who want to more easily update their Facebook Pages on the go: Facebook for iPhone 3.0 , which should be released soon, will include Page updating, ...

Updating trends on mobile
Trends are a great way to find out what people are talking about on Twitter at a given moment and at a single glance. Starting today, we’re making ...

Delhi Metro Fare Calculator and Auto-Updating Map on the App Store on iTunes
Get Delhi Metro Fare Calculator and Auto-Updating Map on the App Store. See screenshots and ratings, and read customer reviews.

BlackBerry Bold 9000 Updating #2 - Flickr - Photo Sharing!
Explore berrytokyo's photos on Flickr. berrytokyo has uploaded 126 photos to Flickr.

LOOT v0.7.0 for Mod Organizer: Updating and Installation - YouTube
A new version of LOOT (Load Order Optimisation Tool) has been released with some major upgrades. This video tutorial shows how to upgrade and ...

Updating Emma: Jane Austen's classic joins list of literary reworks
Jane Austen published six novels. Just six. She died before she completed the seventh.

Resources last updated: 3/21/2016 4:58:28 AM