We help IT Professionals succeed at work.

.ADP Continuous Form (subform) based on server temp table?

n f
n f asked
on
I am creating my first ADP file in Access 2000/SQL Server 2000.  I have a form with unbound controls that will be used to create records in several SQL Server tables.  There is a one-to-many relationship between some of the fields on the main form and the fields I have on my subform.  

So far, the fields on both the form and the subform are unbound, and both the form and the subform are unbound.

In order for the subform to show up as "continuous forms" and allow for many records to be created for each record created on the main form, I'm pretty sure I have to bind the subform to *something*.  In an Access .mdb file, I would bind it to a local temp table, but there are no local tables in an .adp file.  

If I create a temp table on SQL Server using a stored proc, how can I bind the subform to that temp table?  Will each user have his own temp table (that's what I want)?  Should I create the temp table (call a proc that creates it) on the Load event of my form?  Won't the temp table disappear as soon as the proc finishes running?  How do I keep the temp table open until I want to drop it (at the form's close event)?

Since this is a multi-part question, I'm awarding the maximum available points (300) to the first person who gives me a usable answer.  I either need to know how to create, keep open, and bind to a temp server table, or I need an alternative way to have multiple records display on a subform in an Access 2000 .adp file.
Comment
Watch Question

BRONZE EXPERT
Commented:
those are a lot of questions! :)

i do the EXACT same thing that you're trying to do but i don't have the luxury of using a2k. i use a97. the way i get "around" the problem you're describing is by using the logon id of the person's NT account. (could be win9x if you wanted) alternatively you could use the PC's name if those are guaranteed to be unique. another alternative (perhaps the best) is to use the hwnd of the parent form to differentiate your forms.

that being said. here's what i do:

1.) create a PERMANENT temporary table called whatever you want (tblTemp)
2.) add a field to tblTemp called OWNER or something like that.
3.) build your form/subform like you were ONLY going to have one user.
4.) take the RecordSources and copy them down so you can find them
5.) in the OnLoad event of the MAIN form, put this code:
        Me.RecordSource = "SELECT * FROM tblTemp WHERE OWNER = " & me.hwnd
6.) in the OnLoad event of the SUBFORM, put this:
        Me.RecordSource = "SELECT * FROM tblTemp WHERE OWNER = " & Me.parent.hwnd
(i'm using hwnd here, so OWNER would be a long value)
7.) add controls to each MAIN form and SUBform called txtOwner.
8.) in the OnLoad event of the MAIN form, put me.txtOwner = me.hwnd
9.) in the BeforeInsert event of the SUBForm put me.txtOwner = me.parent.hwnd
10.) refresh the forms with me.refresh
11.) in the OnUnload event of the main form, put
       currentdb.execute "DELETE * FROM tblTemp WHERE OWNER = " & me.hwnd

now, whenver a user enters data, only that user will be able to see the data. when the form exits, it will delete anything left in the table.

how my answer stacks up to your questions:
>If I create a temp table on SQL Server using a stored proc, how
>can I bind the subform to that temp table?  
i propose a permanent temporary table, so this isn't necessary

>Will each user have his own temp table (that's what I want)?  
not really, but it'll feel like it to the users.

>Should I create the temp table (call a proc that creates it) on the Load event of my form?
you certainly could, but with my idea you don't need to.

>Won't the temp table disappear as soon as the proc finishes running?
not if you create the table without naming it with # (such as #MyTable) it'll remain until you explicitly destroy the table.

>How do I keep the temp table open until I want to drop it (at the form's close event)?
since the table will be persistent (unless as mentioned above) you will HAVE to destroy the table in the onclose event/OnUnload.

that's my idea. i'll work on a sample for ye and post it.

let me know if this isn't "up your alley"

dovholuk
BRONZE EXPERT

Commented:
here's what i had in mind.

http://www.geocities.com/dovholuk/20205549.mdb

it's in a2k format. it's not an adp as you wouldn't be able to connect to the same db as i do. i think this example is sufficient. it allows you to create multiple windows (simulating multiple users)

let me know what you think...

dovholuk
n f

Author

Commented:
dovholuk,

I'm still hoping someone can show me how to use a server temp table as the recordsource, but, if no one can do that, I'll give you the points.

Your answer is certainly a very nice solution to the problem, but I'd really like to find out the answers to the questions I asked.

nfeldman
BRONZE EXPERT

Commented:
>I'd really like to find out the answers to the questions I asked.
i understand. sometimes though, the answer is that it can't be done...

let me give your questions another go...
>If I create a temp table on SQL Server using a stored proc,
>how can I bind the subform to that temp table?  
what you do is set the form's rowsource to the new temp table. i can't verify this until i get home, but you could try something like Me.recordsource = "SELECT * from [tempDB].[#TempTableName]" '(or something like that...)

>Will each user have his own temp table (that's what I want)?  
if you create temporary tables in the tempdb database, i believe each user will have his own table.

Should I create the temp table (call a proc that creates it) on the Load event of my form?
i would. i would then set the recordsource as i indicated above. you'll have to dynamically set the recordsouce though, you can put it into the form property beforehand.

Won't the temp table disappear as soon as the proc finishes running?
i believe that as soon as the connection is destroyed, so are the temp tables. so they will persist for the duration of the connection.

How do I keep the temp table open until I want to drop it (at the form's close event)?
i would say you won't need to "clean up" because when the user logs out, the temp tables are destroyed for you by the backend.

when i get home (3-4 hours from now) i'll see what i can come up with. but in order for me to help answer this one question. did you plan to use the tempDB database by creating a table named as #TableName? if you aren't, what is your idea?

that's all for now...
ciao

dovholuk
n f

Author

Commented:
n f

Author

Commented:
>did you plan to use the tempDB database by creating a table named as #TableName?
if you aren't, what is your idea?

As far as I know, all temp tables on the server have to start with a "#", so, yeah, "#TableName" is my guess as well.  I've created temp tables on the server with stored procedures, but I can't seem to reference them in order to use them as the recordsource for my forms.  I think that's because they get destroyed as soon as the proc finishes running.

I've tried having the stored proc that creates the temp table end with a "Select * from #TableName" statement, and that lets me use the recordset from the temp table as a form recordsource, but, since the temp table has already been destroyed (when the proc finished), the recordset isn't updateable.

This kind of functionality is so basic to Access .mdb forms that I just can't believe that there's no way to do the equivalent in an .adp!
BRONZE EXPERT

Commented:
personally i'd go for the method i mentioned above... i know it's not the ideal solution for you, but i've read that using the tempdb is a "system hog" of sorts and that using it heavilly is to be discouraged... *shrug*

here's a good link on the tempdb by www.SQL-server-performance.com

http://www.sql-server-performance.com/tempdb.asp

good luck...

dovholuk
n f

Author

Commented:
n f

Author

Commented:
dovholuk,

Not sure why half of my comments get posted as "no text" on this board!

You provided a lot of very good and useful information.  Thanks!

I tried your solution with a few minor tweaks for an Access 2000 .adp file (like using an ADO connection instead of CurrentDB) and it worked brilliantly.

A very efficient solution to a very frustrating problem!

nfeldman
BRONZE EXPERT

Commented:
the "no text" issue HAD never occurred to me before yesterday. i'd seen / heard about it though.

yesterday i experienced the issue first hand... VERY frustrating! ;)

i'm glad i could be of assistance and that you were able to adapt the example into an .adp.

the best part is that any indexes you create and any server-side cacheing persist, making your db run faster! :)

enjoy,

dovholuk
n f

Author

Commented:
I've shared your solution with a few other developers that I'm working with.  

The unanimous opinion is that it's really quite brilliant!
BRONZE EXPERT

Commented:
i never got an e-mail notif. about this question.

*blush* but thanks for the compliment... ;)

dovholuk

Explore More ContentExplore courses, solutions, and other research materials related to this topic.