| Author |
Topic |
|
PETE314
Starting Member
37 Posts |
 Posted - 2005-04-18 : 10:16:56
|
| Access 2000 ProjectSQL 2000 BackendOK this is an mdb conversion to an adp. Basically I am building a form where a user can select records click a button and then code will mark those invoces paid within the Invoices table and then add data into a Payments table.My problem is that I cannot have the selection of records done by the highlighting of the records and then ctrl-select or shift-select of the other records. going by the people who will have to use this software that will not be an option because it will be a bit confusiong for the non computer literate crowd that will have to use the software.So in the mdb in order to create the list I created a temptable and I had a bln field. The user would click the chkbox of the records thewy wanted to pay and hit the button and bing bang boom the invoices are paid.In my adp, I have a couple User defined functions(as I have parameter queries) that gather some data and then I have a stored procedure that takes that data as well as data from other tables. This creates the list that the users can choose from. The stored proc goes as such....creates a temp table (ie... #tablename )uses an insert into to slam data into the temp tablecalls all records from the temp table.unfortunately the resultset is not updateable. So you cannot check off chkboxs.So what I need is an updateable recordset built from several parameter queries that is temporary.Right now the only thing I can think of is a very UGLY solution....which is to create a permanant table(instead of a temptable) with a suffix (ie...tablenameXXXX where XXXX is a random number.) and then delete the table when it is not needed anymore. It is a solution that would work but it is not a preferable solution.any ideas? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-18 : 11:08:02
|
| you need to put a primary key on your temp table before it can be updateable by MS Access. Just add an alter table statement to add a PK constraint on the table after you create it, and it should be fully updateable.- Jeff |
 |
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-04-19 : 19:53:36
|
| When I first saw this I was like "Holy Cow how did I miss that????" But when trying to update the recordset I am getting an Error saying"Database name 'AbsTracker' ignored referencing object in tempdb" but then it doesn't do anything...am I missing a setting somewhere????AbsTracker of course is the name of the SQL DB. |
|
|
|
bferriter
Starting Member
34 Posts |
Posted - 2005-04-20 : 13:05:31
|
| You can designate a table to use as a temp table... design it, assign primary keys so it is updateble, then when the form loads you can run a query in the backround to delete all records, and repopulate it with whatever information you need.If you are going to have multiple personel using it at the same time, don't use a global table (off the sql server), create a resident local one in the access database so each client has their own.. that way you dont need to create a table each time and delete it.. and if two people run it at the same time it wont interfere with the other one's actions. I do this for reports all the time, when I need to format information with out changing the base information from multiple tables. I will populate a "temp" table (that is a permenant resident) with information from multiple queries and use the temp information to generate the report. |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-04-20 : 14:12:30
|
| This is an Access Project.... and adp..... there are no client side tables and it will be multi user |
|
|
|
bferriter
Starting Member
34 Posts |
Posted - 2005-04-20 : 15:37:14
|
| not sure what you mean by adp. i only know adp as the program my company uses to poll punch tickets for the employees timeclocks.when you open the database window in access and view tables, you can create local tables to the access database. thats how you have an access database without a sql server for single user applications (like quickbooks or act)... your temp table only needs to be available to the local client. if you do what i do, that is have a master copy of the access database, modify it, and then save copies on all the client machines, then this will work.. you could have the temp table on the sql server, as a permanent table, but then if two clients run at the same time they will interfere with each other. that is why a local table works well. if you want me to run through this more thoroughly than i will do so. saying "there are no client side tables" is up to you, not a provision to using an access project with a SQL server. |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-04-20 : 17:18:46
|
| Access has 2 ways you can make a database. A file with a .mdb extention is a database that uses the Jet Engine to parse it's queries, hold it's tables and such. When using a SQL Server or equivalent you have to link tables to the Access Database. This is the type of application that you are referencing. But in Access you also have the ability to create a true Client/Server application with an Access Project with an .adp extention on the file. Access ends up being practically just an interface for a SQL Server. It is a direct connection to SQL Server. All the tables are stored on the Server.....all the queries are stored on the Server. You have far more power available to you with the uses of stored procedures,triggers, and the like. You have the power and security of the SQL Server. Another advantage to this is that you have a much smaller file as you only have the code and forms stored in the file.(Yes even smaller than a split .mdb application)The temp tables I have been talking about are stored on the Server itself. As I stated earlier, there are no tables...whatsoever...stored on the client side in an Access Project. I do know exactly what you are talking about....as a matter of fact it was similar to how I ran it as an .mdb application. But as I said in the beginning that this is an .mdb to an .adp conversion so the old solution is not viable anymore.But I do appreciate your help... |
|
|
|
bferriter
Starting Member
34 Posts |
Posted - 2005-04-21 : 08:34:01
|
| i did not understand what adp was... i read your post on tek-tips off a google search for the definition between the two, i understand now you cant have a local table and why.i am sure you already thought about having the nessessary fields added to the existing table and using those as temporary place holders? you could update them as normal, they would not be temporary fields (though the data would be)... you could have the form filter off of those temporary fields and update the table when the user accessed it to which ones showed.. wouldnt you actually already have to do this to keep track of what has been payed and what has not? why did you decide to try to use temp tables for the checkmarks, how are you keeping track of whats already been paid to generate the list? would it be possible to use whether is already been paid or not as the filter for what shows up as a check box? you could have a form with continous records based on whether the bln was boxed or not. |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-04-21 : 10:47:49
|
| Well basically this engine( I call it the Reconciliation Engine) is serving 2 functions. When the user checks the check boxes and then presses a button on the form, the code behind the button will do a couple of things. First it will update the invoice table and click off the paid field to idicate that the invoice is fully paid. It then adds a record to a Payments table idicating the full amount of what is currently owed. I say currently owed because the user has the ability to partially pay an invoice(which is the second function). It also updates a Status table to show that the Invoice has been paid and therefore uneditable(unless by a DBadmin going behind the scenes).Now the reason I do not just use the Invoice table's Paid field is because I need an updateable recordset to build the form off of. And while an mdb. I had the temp table ability on the client side to help me out. But I am gaining too much in the other parts of the application by switching to an adp and the ADO connectivity(instead of ODBC)as well as the power of the stored procedures, triggers, user defined functions, security, etc., etc., etc. that I cannot hold the entire program up just for this section. So in comes the temp tables that SQL creates.So now I have my temptable and I have my Primary Key set(how I missed that I don't know...lol). But when I change data in a field and then try to move out of that record.....I get locked up....so to speak. I get an error stating "The database name 'AbsTracker'ignored, referencing the object in tempdb." The only problem is that when I click on ok...nothing happens. I try to move out of the record...and the same thing...it won't let me out of the record until I set the field back to its original value. So obviously there is a hangup when the record is trying to be updated. and while the recorset might be updateable(It should be with the PK and such) there is definitely an issue when trying to update.Now I have been doing some research on MSDN and there might be a problem as, I believe the temptable loses scope as soon as the stored procedure has run its course. But this conflicts with something I read where the temptable stays in the temp DB for the duration of the connection(however that connection might end when the stored proc has completed.)So I am still in the situation of needing an updateable recordset in which to build this form from. |
|
|
|
bferriter
Starting Member
34 Posts |
Posted - 2005-04-21 : 12:34:10
|
| you said your invoice table is not updatable?? i am guessing you used a standard int field for the id / primary key with autonumber? I have not worked with temp tables, so i wont waste anymore of your time. I will say that I have had trouble using the autonumber column as a searchable field, it may not be able to reference the table by that column once it creates the records. have you tried using the primary key (with autonumber) to build the table but referencing the records off of another column? I also found these links:http://sqlteam.com/item.asp?ItemID=2029 - how to use temp tables..http://dbforums.com/t812419.html - suggests using openquery |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-04-21 : 12:53:01
|
| Thanks for the links I will look into them......My invoice table is updateable but my results recordset(the one to build the form off of....hence the need for a temp table) is not because of the different tables and User defined functions that I have to pull from. |
|
|
|
bferriter
Starting Member
34 Posts |
Posted - 2005-04-21 : 15:42:55
|
| have you thought about using "bit" columns for the check boxes instead of "bln" (thats boolean right)? just make sure you specify a default when you create the table. |
|
|
|
PETE314
Starting Member
37 Posts |
Posted - 2005-04-21 : 16:41:58
|
| well in SQL I don't believe there is a bln(boolean) field...I am just used to calling them that....I do use a bit field. |
|
|
|
bferriter
Starting Member
34 Posts |
Posted - 2005-04-21 : 16:57:23
|
| you have to specify defaults for that column or will will have difficulty using it. |
|
|
|
|
Reconcilliation Engine..........