|
|
Here is what I want to do. I have two separate Access tables.
tbl_One:
RecID primary key for tbl_One
PartNo
Description
<br>
tbl_Two:
ID primary key for tbl_Two
RecID
TypeID</br>
tbl_Two's RecID is taken from RecID in tbl_One. I am doing an INSERT INTO tbl_One (PartNo,Description) VALUES (1111,"First Item").
tbl_One and tbl_Two share a relationship, One-Many, (NO enforcement of Referential Integrity).
How can I get the primary key value (tbl_One's RecID) so that I can also use it when I update (INSERT INTO tbl_Two)?
I'm doing this programmatically from a form that supports tbl_One, but the SQL statement for doing this will definitely help.
Thanks!
Johnny
|
|
|
|
|
You cannot insert into two tables at the same time. You have to execute the INSERT on the first table, get the key generated for that record, then execute the second INSERT on the second table.
|
|
|
|
|
would it be wise to use a trigger in this instance? i haven't worked with triggers as yet but i have to in a project that is coming up... so i would just like to know if triggers would be a good idea in this instance?
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
If he was using SQL Server, I'd say maybe. A stored procedure doing the insert would probably be more prudent, but a trigger is also possible, yes.
Since he's using Access, this isn't possible. Access (Jet database engine) does not support triggers, nor do is there support for "true" stored procedures, so this isn't an option here.
|
|
|
|
|
Thanks for the help/suggestions.
Is there a quick way to get the Key created by the INSERT so that I can readily use it for my field in the second table?
V/R,
John
|
|
|
|
|
|
Thanks. Here is what I ended up doing (similar to what you proposed/found).
I used a recordset pointer to open tbl_One and get the primary key of the last entry. I then incremented this primary key and KEPT A RECORD OF IT so that I can use it in both tables. Since I am working in a loop and doing multiple INSERT INTO statements, I just increment primary key each time and I get the results I desire.
Access VBA code
Dim rstLookupMainTable As New ADODB.Recordset
rstLookupMainTable.Open "tbl_One", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rstLookupMainTable.MoveLast
If rstLookupMainTable.EOF <> True Then
nPrimaryKey = rstLookupMainTable.Fields(0).Value 'primary key field
Else
nPrimaryKey = 0
End If
rstLookupMainTable.Close
I then proceed with nPrimaryKey and perform my INSERT INTO tbl_One making sure to include the primary key field
Thanks,
John John
|
|
|
|
|
john john mackey wrote: Since I am working in a loop and doing multiple INSERT INTO statements, I just increment primary key each time and I get the results I desire.
That's a really, really bad idea. The database doesn't keep records in any particular order in the table, so the key in the last record may not be the actual last key used. You ALWAYS let the database assign the keys and get it from the database when it's assigned. NEVER assume that the (last key you got + 1) is going to be the next key you're able to use.
This concept will also work even worse when the number of clients using the database exceeds 1.
|
|
|
|
|
hi guys
i have some problem regarding sql
i want to print the column names of a table,i know table name i dont know columns.
thanks in advance
regards
|
|
|
|
|
AVI51 wrote: i want to print the column names of a table,i know table name i dont know columns.
Since you posted this in the SQL forum, I am assuming that what you actually want is a way to get the column names out of the database rather than any particular presentation of the column names.
If you are using SQL Server (which you don't say) you can use the views in INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName
|
|
|
|
|
try this
sp_columns 'TableName'
|
|
|
|
|
you can execute
sp_help <tablename></tablename>
or
1.you can type the table name in query analyzer
2.select the table name and press alt+f1 keys
this works fine for me
hope this helps
|
|
|
|
|
hello guys, whats going on ? I have some questions I'd be very happy to someone helps me out !!!
what is the term "ARC", "super entity", "sub entity" if its possible give an example so I can get it better !!!
Thanx Guys !
Mr.K
|
|
|
|
|
|
I want to write a program to run SQL*PLUS on UNIX operating system. Before I run it, I should setting the system environment variables such as "ORACLE_HOME", "ORACLE_SID" first. But, I met with a question, I don't know from which profile I can get these information? Who can give me some suggestion?
Many thanks.
|
|
|
|
|
I don't understand your usage of the term 'profile' in your question. But the two variables you are trying to set are needed to identify the location of Oracle software, ie. where has Oracle been installed?. Talk to your DBA and find out this location. The second parameter identifies the database that you wish to connect to. There may be many instances of Oracle databases running, so again you will need to confer with your DBA to find out the name of the instance that you wish to connect to.
|
|
|
|
|
Well, may be you misunderstand my meanings. After the DBA installed the oracle software, he set these two variables. I think this information(ORACLE_HOME,ORACLE_SID) might be saved in a profile by the oracle process. And when I want to run the SQL*PLUS, I first read these information from this file, because the login user has changed, and the environment variable might be changed too. But I didn't know whether this file exist or not and its path. Do you have any idea about it?
Many thanks
|
|
|
|
|
Hi,
Can SQL Server 2005 receive emails with INSERT statements?
I am trying to insert data into SQL Server 2005 from a non-windows platform.
It seems that I have two options, either the SQL mail(if it can recevie emails) or the Endpoints feature. Does anyone has worked on similar solutions? Any other way to approach the issue? Please give insights into!!
thanks in advance,
-matt-
|
|
|
|
|
obymathew wrote: Can SQL Server 2005 receive emails with INSERT statements?
No. It can only send mail.
obymathew wrote: I am trying to insert data into SQL Server 2005 from a non-windows platform.
It seems that I have two options, either the SQL mail(if it can recevie emails) or the Endpoints feature. Does anyone has worked on similar solutions? Any other way to approach the issue? Please give insights into!!
Web Services might be another approach. I would, however, be very wary of allowing an external process supply raw SQL Statements. If you can get it to call stored procedures only then it might be a bit more secure.
|
|
|
|
|
Voted down to shoot the messenger?
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike Dimmick wrote: Voted down to shoot the messenger?
I dunno. It happens. I no longer really care. I got a spate of ones for a period of days during the summer. I guess I must have upset somebody. I find that I'm growing more intolerant of people who refuse to learn stuff, (as you can probably tell from my blog) and I probably upset one of them.
Curiously, I was talking to our new hire at the office Christmas party about what he thought of our interview process (mainly because I designed a fair few of the questions). He said he was really apprehensive about the technical test and was really worried about it, but when he saw the specification I handed him he suddenly realised it was a piece of piss. (Which it is! It isn't designed to trip anybody up - Everyone in the development team has taken the test, or varients of it as it was being developed). Anyway, the point about the aprehensiveness has been taken on board so next time round we'll be supplying a bit of information to the recruitment agent to give to the candidate to alleviate any fears any future candidates may have about it.
|
|
|
|
|
Microsoft have a JDBC Driver[^] if you're using Java.
Other than that you will have to implement some form of front-end server software - possibly using Web Services, as Colin suggests - to receive and translate queries.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
hi
how to declare and read array in sql server 2000
|
|
|
|
|
There is no inbuilt support for arrays in SQL Server. If your "array" is a comma-delimited string ov values like "va11, val2, val3" etc, you can use the string split functions available in the net.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
