building a link to table in another db from code


i have two tables that only need to exist for the length of the user's session. i build them with ADOX in my backend database when the user starts up, and destroy them with ADOX when the user closes the application. the name of the table created varies, too, depending on the user (userID is built into table name).

i need combo boxes on my forms in the front end to be able to access this data.

is there a way in code, once the temp tables are built, to create a 'link tables' type of setup

my alternative is to build the temp tables in the front end portion, but i don't really want to do that.....
Posted On: Monday 22nd of October 2012 05:57:10 AM Total Views:  281
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Is it possible to link a form to 10.000 sub folders?

My goal and my problem is that i have a folder wich has about 10000 sub-folders with all a name with 10 numbers. Now I want to be able to go to a form fill in a number and then that sub folder pops up. Is this possible Thx, Ron this is what i have and it just brings me to the main folder and doesn;t open the folder of wich I typed the name in.. Option Compare Database Private Sub Command0_Click() On Error GoTo Err_Command0_Click Call Shell("explorer.exe F:data\Wpl\projecten", 1) Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub Private Sub Command4_Click() Me.CommonDialog3.InitDir = "C:\images" Me.CommonDialog3.ShowOpen Me.Text1 = CommonDialog3.FileName End Sub Private Sub Text1_Click() Application.FollowHyperlink Me.Text1.Value I also don;t know the code for a clear buttem to clear the current text on the textbar so you can search for a new one. Any suggestions End Sub
VIEWS ON THIS POST

154

Posted on:

Wednesday 17th October 2012
View Replies!

Pass an Access field as a parameter in link to SSRS or ASP?

I have created a new Access project for creating Customer Order Notes. From Access, I need to be able to open an external report (like .asp or Reporting Services) and use a parameter from the Access Form that I have open. How could I pass a parameter like this to a link Example: I use Access to create a new "Order Note" (OrderID:27) , and at the end.. I want to open a report that will use this new note in the parameter. hxxp://asp_server/ordernotes/print_new_ordernote.aspOrderID=27 or hxxp://ssrs_server/reportserver/MyNewOrderNoteReport&OrderID=27
VIEWS ON THIS POST

143

Posted on:

Wednesday 17th October 2012
View Replies!

3022 error with linked tables

I dont use linked tables very often, and when I always seem to run into the same problem. I have two tables. The pri table has an index key defined as an autoindex, with no duplicates. The sub-table is linked to the pri table key in a many-to-one relationship. The linked field in the subtable is defined as a long integer non-keyed field. is okay, until I attemp to add a SECOND record. At that point I get a 3022 error, stating I cant do something because I am attempting to create a duplicate key. I would love to fix it, nut I am not sure exactly what the problem is that needs to be fixed. The pri key would not SEEM to be getting duplicated, and the subtable linked field is not keyed. Hmmm, I guess I need a steer here please. eatc7402
VIEWS ON THIS POST

177

Posted on:

Thursday 18th October 2012
View Replies!

Combo Box Based on Linked Table needs to pull info for Sub-Form from non linked table

I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in (Dang that still sounds evil and definately NOT understandable even after edit... so) Here's some basic info Tables EstimatesandParts - Table EstimatesandParts_ID : Autonumber Estimate_ID : Number Part_ID : Number Parts - Table Part_ID : Autonumber PartNumber : Text (not a number due to some part#s have letters in them) PartName : Text Unit Price : Currency Description : Text Estimates - Table Estimate_ID : Autonumber InvoiceNumber : Text (again can have letters in it) EstimateDate : Date/Time EstimateTime : Date/Time Employee_ID : Number Customer_ID : Number ProblemDescription : Memo Customers - Table Customer_ID : Autonumber FirstName : Text LastName : Text CompanyName : Text Address : Text City : Text Province_State : Text Postal_ZIPCode : Text (CDN Postal codes are letter num letter...) you can see the link table in the EstimatesandParts Table Now I want to use that link to populate a subform in the F_Estimates form Forms SF_Customers - SubForm (all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code) FirstName LastName CompanyName Address City Province_State Postal_ZIPCode SF_Parts - SubForm Default View -Continuous Forms (want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts) Part_ID : Combo Box Control Source - Part_ID Row Source Type - Table/Query Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description; (Pulls info from the table Parts for input into a list of parts to be used on that project) PartName : Text Box UnitPrice : Text Box (here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work ) (have tried a couple things to complete this task) Me.txtPartName = Me.Part_ID.Column(2) Me.txtUnitPrice = Me.Part_ID.Column(3) (works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...) (tried to make control source for the txtPartName to) =Forms!Parts!Partname (Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry) F_Estimates - Form Estimate_ID InvioceNumber EstimateDate EstimateTime ProblemDescription (all basic Text Boxes) Employee_ID Customer_ID (Combo Boxes Select Customer and Employee from list of present ones of each) SF_Customers SF_Parts (Both SubForms on the main form) Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ... Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
VIEWS ON THIS POST

142

Posted on:

Thursday 18th October 2012
View Replies!

Query from linked data

I am trying to generate a query from a series of linked tables in a "linear" arrangement. My central table is an employee table. I then have two branches for phone data and office data, each in a many to many relationship with the employee table, so I have a linking table between. PhoneEmployee_PhoneEmployeeEmployee_OfficeOffice I am trying to generate a directory that lists for each employee their phone number and their office number. Whenever I try to generate my queries however, I get each phone record for each office record. If I have 4 phones and 2 offices for an employee, I then get 8 rows in the query. Is my table setup just awful (my attempt at normalizing my data as many offices and phones are shared) or is there a clever way to generate a query I haven't yet discovered.
VIEWS ON THIS POST

261

Posted on:

Thursday 18th October 2012
View Replies!

Is it possible to link a form from a table?

I am pretty sure the answer to this is no but I need to ask anyway. Is there a way that your tables can be set up so clicking on a record will take you to the corresponding record on the form (like you can do in Filemaker). I have a client who doesn't want queries or reports set up (!!) just wants to be able to filter and search using the table and then link to the form...
VIEWS ON THIS POST

125

Posted on:

Thursday 18th October 2012
View Replies!

Format problem on linked table

My Access DB has two tables linked to excel spreadsheets. Excel is the only way I can acquire this data from another source. The Excel spreadsheets update themselves on a regular basis. My problem is that one of the fields in the spreadsheet needs to be formatted as a date and I have been unable to get Excel to handle this automatically. Is there some way that I can date format a field in a table that is linked to an Excel spreadsheet Unless I can figure this out, I have to manually be involved with each Excel Update.
VIEWS ON THIS POST

168

Posted on:

Thursday 18th October 2012
View Replies!

Problem: Reports linked to forms

Heres the situation.. I have an input form where all the data entered gets transferred to a single table. I also have a report created so I can print it off and view whatever information has been entered onto the form At the moment when I go to view the report it is showing every record that I have previously entered using that form but I want it to only show the record I am currently entering. The record source for the report is the table where the data is being stored. Hope this makes sense to you .
VIEWS ON THIS POST

168

Posted on:

Thursday 18th October 2012
View Replies!

I need to link fields in different tables

Hi Can you tell me if the following is possible and if so how to do it. I have several different tables which require a date to be entered. 99% of the time this date is the same ie (Table 1) Visit Date = (Table 2) Scan date 1 (Table 1) Visit Date = (Table 3) Scan date 2 etc..... However in some cases Scan date 1 and/or Scan date 2 may not be the same as Visit date so the input does need to be editable. Is there anyway I can link the information in Table 1 to automatically be displayed and stored in Table 2/3 and to be able to edit Tables 2/3 if the scan date is different to the visit date which it could be for a small number of records. Is this possible or is it simply impossible to do this.
VIEWS ON THIS POST

181

Posted on:

Thursday 18th October 2012
View Replies!

linked tables locked when in use

I have the following set-up: an ASP page that connects to an main Access database with linked tables form another Access database. When the main database is in use from Access the ASP page can still get the information from the dbase but when the linked dbase is in use from Access an error returns that the file (linked dbase) is already in use! I use a dns-less connection. Any know what goes wong here
VIEWS ON THIS POST

144

Posted on:

Thursday 18th October 2012
View Replies!

Data link/import from web served excel file.

I need to pull data into an Access database. Currently, the data is available on an excel file being served from the company intranet. (Um, by that, I mean, to open the file in Excel, I have to type "http://intranetserver/path/excelfile.xls") Neither Get external Data Link or Import seems to be able to open this "http:..." name. Ultimately, I want this to be an automated process. The user runs an Access macro, and it will read the data, join it with other data, and save the result in a table. The best idea I can think of is opening excel in my macro, and letting excel open the http:... file, then save a copy of it on a network share, so access can see it. But that seems an ugly hack. Any better ideas
VIEWS ON THIS POST

147

Posted on:

Thursday 18th October 2012
View Replies!

URL link

I have a form with a button on it...when clicked it opens Internet Explorer and navigates to a specific URL. My issuue is that IE is not being pushed the front when opening. How can I make it open and be the front active window This is the code I am using: Code: If IsNull(Me.license_number2) Then MsgBox " You have to select an applicant first" Else Dim varPID As String varPID = pid Dim strPath As String ' Google maps URL strPath = "http://gis.org/grove/map/PID=" strPath = strPath & varPID Dim IE Set IE = CreateObject("InternetExplorer.Application") IE.navigate strPath IE.Visible = True End If
VIEWS ON THIS POST

83

Posted on:

Monday 22nd October 2012
View Replies!

TextBox to show display text for a hyperlink

Hi , First I would like to thank you for taking the time and reading this. My problem is that I am making a form that takes data from one table where the field is a hyperlink and i'm putting that hyperlink as a string in a textbox on a form. The problem is that the string displays the value as display name#URL. for example: google#www.google.com. I only want it to display the display name (google). This is the code i'm using. Code: Private Sub Project_AfterUpdate() Dim strProject As String strProject = Me.Project.Column(2) Me!IMProject = strProject End Sub Thank you for your time.
VIEWS ON THIS POST

109

Posted on:

Monday 22nd October 2012
View Replies!

How can you set a timeout for Application.FollowHyperlink?

Is there any timeout property for Application.FollowHyperlink If there is no way to set a timeout, can anyone suggest an alternative solution I could run the code in a Timed Loop; however, I'd rather use inherant properties to do this is one exists...
VIEWS ON THIS POST

144

Posted on:

Monday 22nd October 2012
View Replies!

Refreshing linked tables on a password protected back end?

I'm using Access 2000. My database is split between the front end and the back end. When the front end is loaded the file location of the back end is checked. If the back end doesn't exist, a form is loaded which allows the user to enter a new file location. This all works fine, however as the back end has a password on it, when the link tries to refresh, for each table I get an invalid password error message. How can I get past this error Can I specifiy the password in my code If so, how do I pass this into the back end db before the tables are refreshed Here is my code: Code: Dim dbs As Database Dim tdf As TableDef ' Loop through all tables in the database. Set dbs = CurrentDb For Each tdf In dbs.TableDefs ' If the table has a connect string, it's a linked table. If Len(tdf.Connect) > 0 Then tdf.Connect = ";DATABASE=" & FilePath & "\" & FileName & ".mdb" Err = 0 On Error Resume Next tdf.RefreshLink ' Relink the table. If Err 0 Then MsgBox Err.Description End If End If Next tdf As always,
VIEWS ON THIS POST

154

Posted on:

Monday 22nd October 2012
View Replies!

Display of different informaton from same linking table

First thanks in advance for any help that you can give..... I am not new to Relational Database Programming but am new to Access. I have a small application i am trying to develop and have all the databases planned and created and all the forms now done. The problem comes to reports - let me explain. Simple courier shipment program. DATABASES ------------ CONTACT table primary key on contact-id SHIPMENT-TYPE table primary key on ship-type SHIPMENT table primary key is auto-generated numeric key FORM ------- User is to key into the shipment table which contains a SHIPPER-ID that look ups into CONTACT but only stores the contact-id, BILL-TO that look ups into CONTACT but only stores the contact-id, and RECEIVER again that looks up into CONTACT but only stores the contact-id. The remainder information for # of peices, weights, time, date are all okay. REPORT ---------- In trying to design the waybill or shipment label i am finding that i can only pull the contact information of address, city, zip etc.... for one source etc...SHIPPER-ID and anytime i pull the address, city, zip for the for the report for RECEIVER or BILL-TO is still just displays/prints the SHIPPER-ID information.
VIEWS ON THIS POST

146

Posted on:

Monday 22nd October 2012
View Replies!

Hyperlink in Report?

, Is there a way to get a hyperlink to work on a report viewed in access I am trying to see if it is possible to have a link on a report that opens a certain form. I saw a topic on MS site that said something about hyperlinks did not function when viewed on a report in access. Just wanted to check if there was a different way to do this or not.
VIEWS ON THIS POST

107

Posted on:

Monday 22nd October 2012
View Replies!

Multiple link import

Hi I have a situation whereby I need to create a db that will link to a directory of images, I have successfully got access to link to an image and create a record but to add 1 image at a time would be unproductive. The user should be able to highlight multiple images and then have access create a new record for each image (link). Any support or suggestions would be greatfully accepted.
VIEWS ON THIS POST

81

Posted on:

Monday 22nd October 2012
View Replies!

Hyperlinks

One more question.... I created a table with one field being a hyperlink I created a query to query out specific fields including the hyperlink. I then create a subForm on a form and point to the query. Everythign works great except the Hyperlink. It just stops working....I can Delete the Hyperlink field and recreate it then delete the subform and recreate it and it works once.... But then Stops....any thoughts... I dont know what else to do...I am currently returning multiple records into a Subform based on a query. I did it this way becuase I can click individual fields, including the hyperlink fields. If I use a List Box then I can select the whole row. AnYone know why the hyperlinks stop working...or can throw another idea out there of a better way to do this.
VIEWS ON THIS POST

81

Posted on:

Monday 22nd October 2012
View Replies!

Editing a ASP dynamic list menu in Dreamweaver linked to an Access DB

I have to translate a dynamic list menu in a asp page, but the menu seems not to get the displayed selection labels from the database. Is there other way to source for the menu labels and How can I chage these labels. here is a link to the page and the menu is at top left. http://www.shopbasis.com/kfzx/start.asp I would apreciate some help.
VIEWS ON THIS POST

105

Posted on:

Monday 22nd October 2012
View Replies!