Solved

Access frontend with a SQL Server backend

Posted on 2003-11-20
9
2,007 Views
Last Modified: 2007-12-19
I have an MS Access frontend with a SQL Server backend. On several of my forms, I have a textbox bound to the primary key of the table. When the backend was solely in MS Access, the primary key would show up as soon as you start making the record. But this is not the case now that the backend is in SQL. The primary key value doesn't show up until you move off the record and comer back to it.

My problem is that I make use of the primary key value before the user gets off the record, so since it is NULL now - my code is crashing. Is there a solution to this.
0
Comment
Question by:tyy8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9788937
Hey tyy8!

 You will have to modify your code somehow to work with this change in MO by the two different databases.
 Is there any other way to identify the record recently entered to return to IT specifically?
regards
Jack
0
 

Author Comment

by:tyy8
ID: 9789705
I need to send the primary key into some other tables, so I need to set that up before the user leaves the record and goes to view those other tables.
0
 
LVL 32

Accepted Solution

by:
jadedata earned 38 total points
ID: 9789733
Set up an SQL trigger on the table to Stick this number into the other table as soon as its available.  The Access code that does this now would then become obsolete because SQL Server would handle it.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:tyy8
ID: 9789971
But I also need to send that users ID (for tracking the user's activity), and some other stuff that the trigger would never have available to it.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9789984
I guess I just don't understand all that is in play here...
0
 
LVL 2

Assisted Solution

by:thorkyl
thorkyl earned 37 total points
ID: 9798989
if me.dirty then me.dirty=false

now the record is saved and you have your key

now you can do what you need to with it.

or as stated above

Create and AFTER INSERT trigger

DECLARE @PK int
AS
@PK=@@IDENTITY
--D0 you your thing using the new key @PK
0
 
LVL 9

Expert Comment

by:ornicar
ID: 10066325
----------------------------------------------------------------------------------------
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10083886
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: jadedata {http:#9789733} & thorkyl {http:#9798989}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
My Do While is Not Producing Anything 15 87
Balance After Payment 12 62
Access Runtime Error '3070' 5 51
Reoccurring Access Query 24 47
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question