f



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 "grade" column with some numbers (1 to 12)? 
Following updates the whole table with same number.  I want it to
randomize each row.

UPDATE TABLEB SET GRADE = (int(rnd()*12)+1);

Thanks in advance.
0
dejauser (3)
12/14/2003 10:20:33 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

9 Replies
1065 Views

Similar Articles

[PageSpeed] 48

Did you try using the Query Builder to create such a query and then looking
at its SQL as a model? I have said for years that anyone who does not, but
writes SQL from scratch, is wasting a lot of time and effort.

  Larry Linson
  Microsoft Access MVP

"Deja User" <dejauser@safe-mail.net> wrote in message
news:ba74bae9.0312141420.6e975aee@posting.google.com...
> 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 "grade" column with some numbers (1 to 12)?
> Following updates the whole table with same number.  I want it to
> randomize each row.
>
> UPDATE TABLEB SET GRADE = (int(rnd()*12)+1);
>
> Thanks in advance.


0
bouncer (4168)
12/14/2003 10:38:31 PM
What happens when you get five "John Smith"s?

TC


"Deja User" <dejauser@safe-mail.net> wrote in message
news:ba74bae9.0312141420.6e975aee@posting.google.com...
> 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 "grade" column with some numbers (1 to 12)?
> Following updates the whole table with same number.  I want it to
> randomize each row.
>
> UPDATE TABLEB SET GRADE = (int(rnd()*12)+1);
>
> Thanks in advance.


0
TC
12/14/2003 11:12:12 PM
"Larry  Linson" <bouncer@localhost.not> wrote in
news:HD5Db.10352$xO.5879@nwrddc02.gnilink.net: 

> Did you try using the Query Builder to create such a query and then
> looking at its SQL as a model? I have said for years that anyone who
> does not, but writes SQL from scratch, is wasting a lot of time and
> effort.

Sometimes when using an inherited and not so well-designed db, or when 
using temporary tables 'SQL from scratch' may be quite effective.

    strRS = "SELECT"
    strRS = strRS & vbCrLf & "First(FullName) AS FirstFullName,"
    strRS = strRS & vbCrLf & "First(Surname) AS FirstSurname,"
    strRS = strRS & vbCrLf & "First(FirstName) AS FirstFirstName,"
    strRS = strRS & vbCrLf & "First(BDateBin) AS FirstBDateBin,"
    strRS = strRS & vbCrLf & "IndId,"
    strRS = strRS & vbCrLf & "First(OwnFamily.FamId) AS FirstFamID,"
    strRS = strRS & vbCrLf & "First(ParentsFamily.FamID) AS 
FirstParentsFamID,"
    strRS = strRS & vbCrLf & "First(FathersFamily.FamID) AS 
FirstFathersFamID,"
    strRS = strRS & vbCrLf & "Count(PicData.PicturePath) AS 
CountOfPicturePath"
    strRS = strRS & vbCrLf & "FROM (((((IndData"
    strRS = strRS & vbCrLf & "LEFT JOIN FamData AS ParentsFamily"
    strRS = strRS & vbCrLf & "ON IndData.ParentsFamilyNum = 
ParentsFamily.FamID)"
    strRS = strRS & vbCrLf & "LEFT JOIN FamData AS FathersFamily"
    strRS = strRS & vbCrLf & "ON IndData.FatherNum = FathersFamily.FamID)"
    strRS = strRS & vbCrLf & "LEFT JOIN FamData AS OwnFamily"
    strRS = strRS & vbCrLf & "ON IndData.IndID = OwnFamily.PrimaryNum)"
    strRS = strRS & vbCrLf & "LEFT JOIN PicManyData"
    strRS = strRS & vbCrLf & "ON IndData.IndID = PicManyData.PersonNum)"
    strRS = strRS & vbCrLf & "LEFT JOIN PicData"
    strRS = strRS & vbCrLf & "ON PicManyData.PictureNum = PicData.PicID)"
    strRS = strRS & vbCrLf & "LEFT JOIN FamData ON IndData.IndId = 
FamData.PrimaryNum"
    strRS = strRS & vbCrLf & "WHERE Nz(FamData.WebOption,0) = 0"
    strRS = strRS & vbCrLf & "AND Nz(IndData.WebOption, 0) = 0"
    strRS = strRS & vbCrLf & filter
    strRS = strRS & vbCrLf & "GROUP BY IndData.IndId"
    strRS = strRS & vbCrLf & "ORDER BY First(Surname), First(FirstName), 
First(BDateBin)"

-- 
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
0
12/14/2003 11:21:36 PM
In article <HD5Db.10352$xO.5879@nwrddc02.gnilink.net>
"Larry  Linson" <bouncer@localhost.not> wrote:
>
> Did you try using the Query Builder to create such a query and then looking
> at its SQL as a model? I have said for years that anyone who does not, but
> writes SQL from scratch, is wasting a lot of time and effort.
>
>   Larry Linson
>   Microsoft Access MVP
>
> "Deja User" <dejauser@safe-mail.net> wrote in message
> news:ba74bae9.0312141420.6e975aee@posting.google.com...
> > 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 "grade" column with some numbers (1 to 12)?
> > Following updates the whole table with same number.  I want it to
> > randomize each row.
> >
> > UPDATE TABLEB SET GRADE = (int(rnd()*12)+1);
> >
> > Thanks in advance.


You might be interested in my primer or query building at
http://www.nero-online.org/lastmeasure



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-=-
This message was posted via two or more anonymous remailing services.





















































0
12/15/2003 3:23:45 AM
Anonymous-Remailer@See.Comment.Header (U N Me) wrote:

>You might be interested in my primer or query building at

Do not click on the above URL.

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
0
ttoews (2789)
12/15/2003 3:35:01 AM
"Lyle Fairfield" wrote

 > Sometimes when using an inherited
 > and not so well-designed db, or
 > when using temporary tables 'SQL
 > from scratch' may be quite effective.

If, seriously, you wrote that SQL statement from scratch rather than first
constructing it in Query Builder or if it was not a modification of that
not-so-good-designer's work, then, chances are very good that you wasted a
lot of time and effort.

But, then again, Lyle, you've never claimed NOT to be an SQL masochist, have
you?



0
bouncer (4168)
12/15/2003 4:32:37 AM
Does this help?  A jpeg screenshot of table structure that could work for you
 http://www.cixel.com/roms/ps2emu.jpg

In article <1071448909.220705@teuthos>
"TC" <a@b.c.d> wrote:
>
> What happens when you get five "John Smith"s?
>
> TC
>
>
> "Deja User" <dejauser@safe-mail.net> wrote in message
> news:ba74bae9.0312141420.6e975aee@posting.google.com...
> > 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 "grade" column with some numbers (1 to 12)?
> > Following updates the whole table with same number.  I want it to
> > randomize each row.
> >
> > UPDATE TABLEB SET GRADE = (int(rnd()*12)+1);
> >
> > Thanks in advance.

http://www.nero-online.org/lastmeasure

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-=-
This message was posted via two or more anonymous remailing services.




















































0
12/15/2003 4:37:59 AM
Anonymous-Remailer@See.Comment.Header (U N Me) wrote:

>Does this help?  A jpeg screenshot of table structure that could work for you

Do NOT click on the above URL.

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
0
ttoews (2789)
12/15/2003 6:16:34 AM
Why do these retards post this kind of links ?  Go screw yourself.
0
dejauser (3)
12/17/2003 3:19:28 AM
Reply:

Similar Artilces:

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. ...

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...

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...

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 from dual; Where "test" is the name of an Oracle sequence. berj "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:bp0821lsd8tt5ec5jhvtgbt46fdvb2t3dt@4ax.com... > The easiest way is to link the table in Access, then query the link. Is that > how you're trying to do it? > > On Mon, 28 Feb 2005 21:25:36 -0800, "berj" <berj@adelphia.net> wrote: > > >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 ...

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 I don't know what software is >needed. Do you know? If so, please answer this post. Thanks. I think it comes down to semantics. "Licensed" vs "Loaded". No you can't create an app that will run on a PC with nothing loaded. Yes you can create an app that will run on a PC where nobody's paid the fee to run MS Access. You still have to install something on that PC, however. What it is is a freebie version of MS Access that only runs applications - doesn't allow anybody to develop an app. You develop the app on your PC, which has MS Access installed. Then somebody installs the freebie ("Runtime Access") on somebody's PC and they can run the app you developed without having to pay any money to anybody. -- PeteCresswell Sue, If Access isn't present, some version of it must be installed to run an Access database. VB.NET, ...

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 ...

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 ...

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...

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 ...

Error on MS Access Project when updating table
This is a simple MS Access 2003 Project file (*.adp). It accesses a SQL Server 2005 database on the network. It has 10 tables. Everything updates, deletes, inserts, adds without a hitch, except for the issue(s) below. When I make a selection from the combo box and move to another object or close the form I get the following error: (error 1) "Key value for this row was changed or deleted at the data store. The local row is now deleted." I hit the "OK" button. It takes me back to the form. If I close the form, I get this error: (error 2) "Multiple-step operation generated errors. Check each status value" I hit the "OK" button. Another alter box appears saying, "You can't save this record at this time. <Application Name> may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?" <Yes> <No> I hit "Yes" the alert box appears again. I hit "Yes" again. Another alert box appears with this error message, "Another user or application has deleted this record or changed the value of its primary key." Error 2 appears again. I hit the "OK" button and the form disappears. I look at the data in the table and the new information has been saved/ committed. When I open the form again the data I just entered (after the errors) is there and in t...

How to access automatically in SAS a MS Access table name which has more than 32 characters?
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'n (keep = --------------------------------------------- 211 ERROR 211-185: Invalid data set name. 1641 1642 testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text 1643 ); Jean Baptiste, --- Jean Baptiste KAMGANG, Lead Data Manager - CDC Cameroon US Centers for Disease Control and Prevention Mutengene, SouthWest Province Cameroon, Africa E mail: JBKamgang@cm.cdc.gov. Tel: + 237 3 335 1690 Fax: + 237 3 335 1692 Cell: + 237 9 984 79 14 --- .. Ca...

Re: How to access automatically in SAS a MS Access table name #3
Jean - Unfortunately you are stuck - SAS only allows dataset names up to 32 bytes long - using name literals or conventional names. Defining short-named queries of your tables within MSAccess is a very good idea. The SAS libname engine will read the queries as tables and it doesn't necessitate changing your table names. Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of JKamgang@Yahoo.com Sent: Wednesday, January 16, 2008 9:32 AM To: SAS-L@LISTSERV.UGA.EDU Subject: How to access automatically in SAS a MS Access table name which has more than 32 characters? 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...

Re: How to access automatically in SAS a MS Access table name #2
Hi Jean, It looks like your problem is that you've got a quote in a table name: Additional'n=20 If it is possible to rename that table, that might work. =20 Also, if you use the import feature in SAS, it will write code for you = that you can save and put in a program- you might try that to see if it = can handle this kind of file name. I'd rename the table in Access, = though- a quote in a table name is likely to contiously cause you = problems. -Mary ----- Original Message -----=20 From: JKamgang@Yahoo.com=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Wednesday, January 16, 2008 11:31 AM Subject: How to access automatically in SAS a MS Access table name = which has more than 32 characters? 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=3DANY ; 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.'tblInfantHIVTest...

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...

How can I stop people have access to my MS Access Tables and Queries via Import?
Hi I have an MS Access based application almost ready for distribution to the public and I find that even though I have compiled it into an MDE file, tables and queries can still be be imported if accessed by another MS Access database. How can I stop this please? Regards Carriolan <carriolan@> wrote in message news:pvbbp19hrepj8d02pvdru31rgkvk0o8kg5@4ax.com... > Hi > I have an MS Access based application almost ready for distribution to > the public and I find that even though I have compiled it into an MDE > file, tables and queries can still be be imported if accessed by > another MS Access database. How can I stop this please? > User-level security. Not for the faint-hearted and not resistant to a determined hacker with the right tools but good enough to keep out nosey parkers. Check out the MS FAQ on Access security (link on my web site) and Google Access groups for Jeff Conrad who has some useful stuff too. HTH - Keith. www.keithwilby.com Hi Keith I am going through the MS FAQ as you suggested. Agreed it is not for the faint hearted! I have one more quested. As my program is for distratibution to the general public as shareware, is using this type of security valid? Regards Carriolan On Tue, 6 Dec 2005 16:06:02 -0000, "Keith W" <here@there.com> wrote: ><carriolan@> wrote in message >news:pvbbp19hrepj8d02pvdru31rgkvk0o8kg5@4ax.com... >> Hi >> I have an MS Access based application almost read...

How to read MS Access Database Table Created Date
Hi, may need some help here. I am using Borland Delphi Dev 2006. I am able to read the list of tables names in MS Access 2003 database using ADOConnection1.GetTableNames, but I am trying to read the list of tables with their created/modified dates, appreciate some help here please. Thanks. Xan. ...

How to read MS Access Database Table Created Date
Hi, may need some help here. I am using Borland Delphi Dev 2006. I am able to read the list of tables names in MS Access 2003 database using ADOConnection1.GetTableNames, but I am trying to read the list of tables with their created/modified dates, appreciate some help here please. Thanks. Xan. ...

Update Access Table with Excel Table
I have a table that is being utilized in an Access database. There is a number of updates that need to be made on a monthly basis. The updates are sent in Excel format. Is there a way that I can upload the information from the Excel sheet into the Access table? I tried importing it and it won't let me do that because the it can't override the information in the table. I tried linking the database to an excel spreadsheet, but that slows the database down significantly and removes the ability of multiple people accessing the database at the same time. Preferably I would like to be able to run some code that would update the database table from an Excel spreadsheet. The table has 3 fields: Index, Department, and Responsible_Party. Index is the key. The update should update the department and/or responsible_party fields if they are different in the spreadsheet. It should also enter a new record if an index is not found. Any help on this would be appreciated. The kind of operation(s) you are describing is/are better suited by a server based RDBMS like sql server. Access is a file based RDBMS. There are various workarounds for Access, but these are mainly bandaid fixes - godd if you are only performing these operations once or once in a great while. But for continuous operations - the bandaid fix doesn't really fix anything and causes more problems. The easiest fix would be to step up to a server based system - if these operations are ongoing. Rich *** Sen...

MS Access Question / Multiple Find and Replace Based on Another Table
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data in the format that I need it in order to create the reports that we use. So far this has proven to be successful for the reports that I am doing and the data that I am pulling into it. I just have one challenge that may require a lot of work and I wanted to find out if there was a possible way to save time and a lot of effort on this. I basically need to create either a query or macro that will look ...

command line utility for displaying table names of MS Access database
Can anyone point me to an existing utility or code template to write a utility? I want a command line utility, passing in the MDB filename, producing a file (text or whatever) of tablenames and fieldnames. On 10 Feb 2007 12:57:39 -0800, stephen.anderson@va.gov wrote: If I really wanted a command-line utility, I might consider creating a VBScript file (*.vbs by default) and running that using cscript.exe: cscript c:\test.vbs -i:c:\test.mdb -o:c:\test.txt I think the VBScript file can handle commandline arguments. From that point, I would use DAO or ADOX to get the table information. Those objects would be created using CreateObject. The FileSystemObject can be used to create the output file. -Tom. >Can anyone point me to an existing utility or code template to write a >utility? I want a command line utility, passing in the MDB filename, >producing a file (text or whatever) of tablenames and fieldnames. ...

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. ...

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 ...

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...

Reorganize MS Access Table
I have a table of the structure below: Delivery Product 101 A 101 B 101 C 102 A 102 E 103 C 104 A 104 E 104 C You may see that a delivery can have multiple products. I will like to reorganize the table to look like this: Delivery Product1 Product2 Product3 101 A B C 102 A E - 103 C - - 104 A E C So that there is only one line of record for each delivery. I will greatly appreciate if someone can show me a way (query, etc) to organize the table into this form. Please not each delivey may have a different number of products. Thanks. Seth wrote: > You may see that a delivery can have multiple products. I will like to > reorganize the table to look like this: > > Delivery Product1 Product2 Product3 > 101 A B C > 102 A E - > 103 C - - > 104 A E C > > So that there is only one line of record for each delivery. I will > greatly appreciate if someone can show me a way (query, etc) to > organize the table into this form. Please not each delivey may have a > different number of products. Seth, you do NOT ever want to make your table t...

Web resources about - Update MS Access table from another table - comp.databases.ms-access

Apple releases OS X 10.11.4 El Capitan with Live Photos support for messages and password-protected Notes ...
Juli Clover / MacRumors : Apple releases OS X 10.11.4 El Capitan with Live Photos support for messages and password-protected Notes — Apple Releases ...

Belgian Police Name Man Suspected of Being Salah Abdeslam’s Accomplice
Najim Laachraoui was with Mr. Abdeslam, who is charged with terrorist murder over the Paris attacks, as they passed between Hungary and Austria ...

Expedia Takes Sick Children on Thrilling Real-Time Adventures Without Leaving the Hospital
Children with serious illnesses and confined to their hospital beds might think that swimming with tropical fish or running with wild horses ...

Novak Djokovic Says Some Weird Stuff About Women Tennis Players and Their 'Hormones'
On Monday, number one ranked tennis player Novak Djokovic joined the incomprehensible band of tennis-adjacent figures confidently saying some ...

Ukulele Batman v Unipiper Superman: Dawn of Craziness
Batman is sitting in the park, just minding his own business and playing the Batman theme on his ukulele, when he is suddenly drowned out by ...

South Korea: Prepared for another North Korean nuclear test
A US think-tank said recent satellite images showed activity at the North's main nuclear test site that suggest a further underground detonation ...

Justin Bieber brings out Chance the Rapper and Big Sean on Purpose Tour
When Justin Bieber kicked off his three-night stay at Los Angeles' Staples Center Sunday night, he was in some pretty good company. SEE ALSO: ...

Got An Older Kindle? Update Your Device By March 22 Or It Won’t Connect To The Internet
Unless you’re happy reading the material that’s on your older Kindle right now forever and ever, you’ll want to heed Amazon’s advice, and quick: ...

TV News Roundup: ‘Walking Dead’ Gets Super-Sized Finale, ‘Bloodline’ Sets Season 2 Premiere Date
Netflix’s “Bloodline” sets a Season 2 premiere date; “The Walking Dead” will get an extra-long Season 6 finale; “The Talk” will once again announce ...

‘RRS Boaty McBoatface’: NERC Research Vessel Could End Up With A Joke Name
“RRS Boaty McBoatface” may not be a very regal sounding name for a Royal Research Ship, but if officials at the Natural Environment Research ...

Resources last updated: 3/21/2016 8:08:07 PM