Building A Link To Table In Another Db From Code

Mar 4, 2005

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.....

View Replies


ADVERTISEMENT

Security - Allow Query Building, But Not Table Building/modification

Sep 23, 2006

I've been trying to figure this out, but it looks like a no go.

I wanted to give my users the ability to create/modify queries, but NOT create/modify tables.

Does anyone know if this is possible in Access 2002?

Thanks,

Earl

View 2 Replies View Related

Link Table With Code

Jan 19, 2005

Hi
I am linking av table from Sybase Adaptive server anywhere 8.05 by using this code:

DoCmd.TransferDatabase acLink, "ODBC", "odbc;DSN=database;UID=dba;PWD=sql;DATABASE=databas e", acTable, "trc.ansatt_dato", "trc_ansatt_dato", False

This works just fine, but I gety this dialogbox where I am asked to select a unique keys or something.... .
Is it possible to write a code to automatic choose OK?

Anyone?

eroness

View 2 Replies View Related

Code To Link Text File As Table

Jan 29, 2008

I'm trying to figure out how to link a text file to my database with VBA. I know how to do this for other types of data sources, using the DoCmd.TransferDatabase acLink command, but need guidance using this with TXT files.

Thanks,


Randy

View 3 Replies View Related

Help With Link Code Please...

Dec 28, 2007

I have a FE database that on initial setup the user will enter the drive letter of the mapped location where the BE resides. The FE will then update all the linked tables to that path. This should work but for some reason the FE locks up (stops responding) when I test this. Any suggestions as to why. It doesn't stop responding until this part of the code:

tdf.Refreshlink

I have looked and it does change the link path, but it locks up.

Dim dbs As DAO.database
Dim dataPath As String
Dim tdf As DAO.TableDef
dataPath = Me.drive & ":IMP1_R2.2_be.mdb"
For Each tdf In dbs.TableDefs
'Not a MSys type table
'If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
If InStr(tdf.name, "MSys") = 0 Then
tdf.Connect = ";DATABASE=" & dataPath
End If
err = 0
On Error Resume Next
tdf.RefreshLink
End If
Next tdf
DoCmd.Echo True, "Done"

View 1 Replies View Related

Link Tables Via Code

Feb 7, 2007

Hey everyone I'm new here but pretty familiar with Access etc. I am extrememl;y stumped on this and I have never attempted this so any answers and details would be helpful. As we all know this is a learn new things as you develop type of application or at least for me.

I have a database that exists in one building and is functionnig fine using linked tables (File/Get External Data/Link Tables) but I now need to make this same application work for multiple locations which each have its own tables that are seperate from each other location. I was thinking a Main form that opens with radio buttons and based on the one you select would determine which tables it would be linked to when it opens. I figured Radio1 be SEDC, Radio2 be SWDC.

I have a database I have setup as a storage database for just the tables for each location that are on different servers in each location to increase traffic speeds.

Example:

SEDC (RadioButton1)
SEDC Database is located on a erver with 50 different tables in it "//sedc1/data/shared/warehouse/SEDC Warehouse.mdb"

SWDC (RadioButton2)
SWDC Database is located on server with same tables in it but different data for that location "//swdc1/data/shared/warehouse/SWDC Warehouse.mdb"

So on So on...

View 3 Replies View Related

Broken Link To VB Code

Sep 13, 2005

I am having trouble with a simple data base where I have 1 main form with 1 sub form, but when I try to use the form (go from design view to datasheet view) I get the message

Error Accessing File
Network Connection May have been lost.

and the link between the form and the code seems to have been lost ... I can no longer edit the vb code (it keeps reverting back no matter what I type).

When I delete the form, the VB entry remains - (since it is no longer linked with the form). Running the database this way I get error messages saying there is a Macro with a misspelled name (ie: it can't find the form with the name).

The only way I have been able to get rid of it is to create a new form with nothing in it, save it and then delete it.

Really frustrating!!!!

Any ideas on how I can get rid of this problem, best ways to re-build the data base (I have created a new one, imported the tables and created new forms from scratch ... then this problem comes back@&^^#%$@).

Thanks

View 1 Replies View Related

Building History Table...

Mar 7, 2005

Hello.
I'm building a history table to keep track of some changes that occur on one of my forms.
Here is what I'm using to build my history table:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblDateCycleTestingChanges", dbOpenDynaset)
With rs
.AddNew
![CycleTestingDatesID] = Me.CycleTestingDatesID.Value
![Changed] = Now()
![ClientID] = Me.ClientID.Value
![ProjectID] = Me.ProjectID.Value
![TaskID] = Me.TaskID.Value
![TaskStartDt] = Me.TaskStartDt.Value
![TaskEndDt] = Me.TaskEndDt.Value
.Update
End With
Set rs = Nothing
End If
I have 2 questions:
1. I'm not sure where to insert this event. I'm thinking on Before_Update on the updated field? (I don't want to put it into Before_Update for a form event as I have other things that are being filled out/changed and I only want to keep track if certain fields on the forms are updated/changed.
2. I would also like to keep track of Old and New values for those specific fields. Is that at all possible?
Thanks.

View 5 Replies View Related

Code To Link From Access To Company System

Oct 21, 2004

Through word of mouth I hear that you can creat a link that can go from Access and link to the personal company system. Is this true? If it is, is there a standard code to use?

View 2 Replies View Related

Building A New Table As A Mailing List

Nov 29, 2006

:( Hi Guys,

I have a very basic DB of customers names and addresses. What I want to do is find all the enquiries within the months of August, September and October, send all these enquiries to a new table so that I can use this new table for a mailshot.
The date of the incoming enquiry is in a field on it own and written as dd/mm/2006.
If there is an easy way to create a mailing list from the original table please let me know.
Best Regards
Keith:o

View 1 Replies View Related

Building A Query From Table Fields

Dec 2, 2006

Hi,
I am trying to make a query from fields out of 3 tables.
All tables must include following fields:

Table 1 fields:
WR04 (year 2004)
Date
Reporting Person

Table 2 fields:
WR05
Date
Reporting Person

Table 3 fields:
WR06
Date
Reporting Person

I am trying to pull together in the query any given individual (Reporting Person) who may be included in all above tables but believe I may have a relationship problem as I am only getting results that match all tables.
I hope this is clear.
Any suggestions please.

View 4 Replies View Related

Code To Link Tables - Overwrite Specific Links

Oct 13, 2006

I searched and couldn't find anything on this specific issue.

I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.

The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.

Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.

View 1 Replies View Related

Using A Table For Both Data Entry And Building An SQL String

Oct 8, 2007

I have a table that lists county names. On the data entry form only the county names are displayed. However, I have another form that is used to build a custom query based on various criteria, of which county is one.

To make the table compatible with both both forms, the data source for the dropdown list for data entry is: Select * FROM county WHERE county <> "No Selection"

For the SQL form the code is simply: Select * FROM county The default value of the dropdown list is "No Selection". When dropdown list for county is set to "No Selection" the program interprets it as "select all counties". Other dropdown lists provide other parameters such as the project year, nature of the project etc. Each of these other dropdown lists also have a default value of "No Selection". The SQL form thus gives me the ability to mix and match several criteria. For example, it will display all projects in a particular year for a particular county or all projects in all counties that involve the construction of a residence.

My question, instead of physically having the string "No Selection" in the table itself, is there a way to embed the phrase "No Selection" in the SQL expression itself and still have it as an option on the dropdown list?

View 1 Replies View Related

Modules & VBA :: Building Temporary Table Using Records In Two Different Tables

Sep 23, 2014

I'm making a library database program thing... There's an option for the user to view all books on loan.

I have two tables:

Books, which has columns ID*, ISBN, Author, Title, Year, Location
BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number

Book ID in BorrowerStorage is related to the Books primary key.

Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...

View 5 Replies View Related

Using Boolean 'and' To Update A Table Based On Values In A Link Table

Feb 24, 2008

The attached Access XP file demonstrates my problem. I've included a form to make testing easier.

Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.

A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.

I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.

In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.

I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.

View 5 Replies View Related

Tables :: Two Fields In A Table / Link To Same Lookup Table

Mar 14, 2013

I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"

*Attached Image "Product_StoragePlace" from the Access Relationship Window"*

When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.

View 5 Replies View Related

Table Link

Jun 5, 2007

Is it possible i create one master table and link few table together??
In the mdb that i attached there have 3 table - tblMaster, tblConfiguration1 and tblConfiguration2.
Using the relationship to link tblMaster's field Configuration to other 2 table Configuration field but there only showing 1 table data.
Is it allow in tblMaster able to show this 2 table together??

View 5 Replies View Related

Can't Link New Table

Apr 23, 2007

hi

I have a new table that I added to the back end of my Access 2003 database. I compacted & repaired both the front and back and I can't see the new table in the front end still. Do you know why this is?? I can't find anything on this.
thanks-

View 2 Replies View Related

No Link Table Manager

Sep 6, 2005

I am working on a new system. Have been given Full MS Access but Link Table Manager fails it gives a standard security warning that this new system enforces it says Opening: CProg..FilesMSOfficeOffice11ACWZTOOL.MDE however on selecting OPEN nothing happens. If Cancel is hit then
long critical message saying in short " Can't find wizard , syntax error in delerations in VB. Likewise I cannot link to a spreadsheet without using the manager.

Question is there anything I can do or is it the fact that these are options were not installed and it is an administrators job?

View 3 Replies View Related

Table Link Manager

Jan 31, 2008

I searched the internet and different forums, but could not find the solution for my problem. In Access 2003 I splitted the database and made a mde file of the frontend. When I copy the front and backend to another computer it cannot find the backend due to the original path is included. To solve this I would like the database to link the backend that is available in the directory of the frontend. No more and no less. Is this possible? I find a lot of questions in this direction, but could not destilate a solution.

View 1 Replies View Related

Link Table To Excel

Apr 19, 2005

Access 2000

How do I link a table to excel table so if I add or change things it changes in the other.

Bub :rolleyes:

View 2 Replies View Related

Read Only Table Link

Jun 27, 2005

Is it possible to create a table link that is read only. I realize that there a a number of work-arounds, but I'm curious about this functionality specifically.

View 3 Replies View Related

Link Table To Form

Aug 18, 2005

Hiya

Please be gentle I am no expert at this sort off thing.....

Is there a way of clicking on a line in a table and the relavant form opens in a new window. You can then edit the form and the table is updated.

Thanks

Gibby

View 3 Replies View Related

How To Remove The Table Link?

Jul 18, 2006

anyone know how to remove the table link (not delete linked table) at run time?

View 1 Replies View Related

Link Table Issues

May 2, 2008

I have a frontend and backend database put in a network drive where users can access to the frontend version which has tables linked to the backend.

When I set up the link tables from the frontend to the backend, Access points to a local drive letter in my computer. For example, if I map the network drive where the databases are, say to Drive R:, the link table in the frontend will always point to tables in the backend on R:. If other user map the same network drive to drive letter other than R:, the table links between the frontend and the backend will be broken.

I wonder if anyone knows a way to set up table link with a physical path instead of a local drive letter?

Thanks

Mike :rolleyes:

View 2 Replies View Related

Combo To Link Table

Jun 28, 2006

Hi.. i have a combo1(InvoiceNo) that look up the values in a table, (there are invoices numbers on it) In the Invoices table i have InvoiceNo (that is the key) and other fields like InvoiceCity, SaleDate, etc

I need to store the current date in Invoices.SaleDate = now() in Invoices table that matches with the combo1.

The Form has as RecordSource, Customers Table

Any tip or hint???

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved