Tables :: Copying A Table From One Linked DB To Another

Dec 3, 2013

I am looking to copy one exceptionally large table to another. I can use VBA to copy the entire Report2013.accdb to Report.accdb while compacting at the same time. This is very fast and works well. However once it's copied I need to rename the table tblReport2013 to tblReport. None of these table are in the accdb that I am running the scripts from FrontEnd.accdb. I am working very hard to keep the data tables under the 2 GB limit, which is why linking and keeping the data separate is necessary. There is the chance that Report2013 will be on the network while tblReport will be local to the user, and may or may not be a subset of the Report2013.

I have considered a number of options:
DoCmd.Rename - I can't seem to tell it a database location

I could try
docmd.copyobject - and see if it keeps the file size the same or smaller then the original.

I could just write the sql statement and copy the data that way, and compact the table afterwards. The trouble with doing the transfer that way is hitting the 2GB limit, before I can compact/compress the newly imported data.It just seems odd to have to find another solution when all I really want to to rename a table and ensure the new new is still linked as a separate table.

View Replies


ADVERTISEMENT

Copying Data From A Linked Subform To A New Table

Dec 3, 2005

I have linked a SQL table to my ms access database. In order to view the SQL table as read-only and still be able to update the access table I have setup a form with a subform.
I know it would be a dublication of data but is there a way thru code of copying the data on the subform to my access table. I was hoping I could put a button on the form that would copy this data. My idea is after the copy is done all the information I need will be in my access table. I'm new to this linking idea and am worried I will only have half the information I need if the link breaks etc.
Thanks

View 1 Replies View Related

Modules & VBA :: Copying Records From Linked Table / Each Record Slower And Slower

Jul 30, 2015

I have an access db with a linked table (to Sharepoint) which contains about 15000 records. I have to copy those records to the local access db. To do this I have a bit of VBAcode. It works, but for each next record that is copied it takes a bit longer. When starting the program the records are copied quite fast, but soon you see it slowing down until after a couple of thousand records it just goes too slow to be usefull, meaning it would take a lot of days to copy all records.

What I now do is let it copy 500 records and exit ms-access completely and then start it again and let it copy the next 500 records. This works but is tedious as I have to do it about 35 times. Why this gradual slowdown occurs?

The VBA code is pretty straightforward.(I use VBA because I do a bit of data wrangling while reading in the values of the records)

Code:

Dim importdb1 As DAO.Database
Dim rsimportdb1 As DAO.Recordset
Dim i As Double
Set importdb1 = CurrentDb
Set rsimportdb1 = importdb1.OpenRecordset("2015")
Set rsMain = CurrentDb.OpenRecordset("tbl_import")

[code]....

View 3 Replies View Related

Tables :: Normalize Existing Table Containing Data And Copying PK Value To FK In New Table

Nov 21, 2014

I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.

After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.

I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"

I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.

View 6 Replies View Related

Tables :: Copying Data From One Table To Another

May 28, 2013

I want to be able to copy the data within a selected field and then place it into a fresh database is this easily done?

my second questions is i have several fields which need to be merged into one 'Address' field as currently i have data with Building Name, Number, Line1, Line2, Line3, County, Town, Country. This is required to be merged into one field how would i do this?

Not all fields have data is there a way to put a space between each of the current fields when merged as well?

View 7 Replies View Related

Tables :: Copying Column From One Table To Another

May 4, 2013

Query:
I have two tables : Table1,Table2.
Table1 has columns : A,B
Table2 has columns: A,C

What I want to do is to copy all data of "C" from Table2 to Table1 so that Table1 has columns: A,B,C..(Column "A" is common in both Tables).

View 4 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

May 28, 2015

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

View 8 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Tables :: Data Entered In Table Linked With Other Table

Nov 11, 2013

I have two table

1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID

i want to entered data in Saving with each and every EMPID with Master

View 1 Replies View Related

How To Partition A Table Into Two Linked Tables

Mar 17, 2006

Hi, All,

I have so far only worked with single tables and queries but not with linked tables. Now the following problem has arisen.

We have a database all of whose fields except two are of type = Text. Two fields are type = Memo.

We have to submit this database, i.e. its main table, to an external company for automatic 'cleansing' (UK: Telephone preference service). The cleansing program converts the cleansed database into a comma-delimited text file and returns it to us as such, and we then have to convert it back into an Access table.

(That's the way our supplier works, it is not under our control, and we have to learn to cope with it.)

Now the cleansing program is being screwed up by our Memo fields, because they contain commas. So when the cleansing program sees a comma, it interprets this as the start of a new field, and the same happens when we convert the text file back into a table.

Two solutions were suggested by the cleansing company, but only the following seems suffiently automatic and therefore acceptable to us.

I NEED HELP IN IMPLEMENTING THAT SOLUTION.

-----------------------------

I want to move the two memo columns into a separate table (memo table), linked to the original table (main table). Have the ID column (unique identifier) in both tables to keep the records together. Then I send only the main table for cleansing and the commas in the memo can no longer work havoc.

I have an idea of how to create the memo table: make a copy of the main table and then delete all columns except ID and the two memo columns.

But then I have to link the two tables (I do not know how to do that); where do I start.

I must also ensure that when new records are created (now usually through a form), both tables are expanded. And I must get information from both tables into one form.

At present I do not even know where to start, except for creating the Memo table by copying it and deleting certain columns.

I have a good book (John Viescas: Running MS Access 2000), but it is huge, and the problem is urgent (it stops our tiny company from marketing). I have no time to study the whole book to find what I need in this case. A chapter or page reference would be very useful.

Or some Tutorial on the web that deals with my particular task.

Thanks for your help.

Adrian

View 4 Replies View Related

Main Table Linked To Several Sub-tables - Help!

Sep 2, 2007

Dear All

Since last week I am working on a Database which I will use for my company. To make a long story short, I didn't find any appropiate softwares nor examples on the internet, hence, with my some knowledge in Access, I am trying to make this "to be" useful tool for my self.

Short brief on the project:
This is going to be used for a trading company to register:
- Suppliers
-- Products

- Customers
-- Inquiries
-- Orders
-- Offers

*Relations:
- Suppliers can have one or many products
- Customers can have one or many Orders / Inquiries / Offers

Problems:
(I have tried many different ways, but going nowhere)

1) The "Customers" table have 3 tables (Orders/Inquiries/Offers) linked to it - and all are based on the primary key "CustomerId". --> How can I make a form to enter these data linked?

2) Under "Orders" I want to select supplier from the Supplier table and Product from the Product table -- How?

I know this is a lot of info and probably too vague, but if someone could assist me with a few problems - then I would really really appreciate it and compensate somehow.

Looking forward for your help.

Best regards

View 1 Replies View Related

Putting Linked Tables Into One Table?

Sep 17, 2013

I have multiple tables that are linked to excel. I am creating a product selection tool to make it easy to find the products contained all these linked tables. They are necessary as they contain pricing data and information necessary to be kept in excel which is regularly updated.

Is there a way to create one table containing data from all these linked tables? I tried using an append query but realise that when the linked tables are updated the table containing all the data wont be?

View 3 Replies View Related

Linked Tables Not Updating In Junction Table

Nov 20, 2004

Hi, I have the following structure:

Products 1-M ORDER DETAILS M-1 ORDERS M-1 CUSTOMERS

I have ORDER DETAILS set as a junction table so that many products can be recorded within one order. All is good apart from when i go into ORDERS and create a new order. I click the subform which links to the ORDERDETAILS. I then pick a product number(look up from products table). The problem is this: In the ORDER DETAILS I want to display the unit price of this product simply by picking the product id.

Eventually this would form the basis of an order form where I can pick Product Id and have it display unit price.

Any ideas on this one, I'm sure its quite simple!!

Thanks in advance

View 1 Replies View Related

Do Tables Have To Be Linked To Do A Multiple Table Query?

Sep 13, 2006

Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.

View 1 Replies View Related

Tables :: Deleting And Replacing A Linked Table?

Mar 5, 2014

My table (excel spreadsheet) sits on another directory from the DB. If I delete the table on this directory and immediately replace it with a table with the same filename, format, etc (only the data has changed) will each database user have to relink the table on their desktop DB? Or will the forms/subforms/reports still maintain their relationships/functions and just display the new data?

View 2 Replies View Related

Tables :: How To Check That Linked Table Exists

Apr 16, 2013

Background: In my Access FE, I have created a "linked table" to a file on our AS400 database. I know I can check the MSysObjects table for the linked table name, but sometimes the file actually does not exist on the AS400.

The file can be in one of three states.File exists with data.

File exists, but is empty.

File doesn't exist.

Question: What is the best way to determine the status of this linked table (file)?

View 1 Replies View Related

Tables :: Changing Linked Table Names

Mar 3, 2015

I need to update the names of my ODBC linked tables in my Access database, how can I do this without causing issues with my queries/reports?The current linked tables are to a SQL View on a database called mcsrm_live, and called e.g. vwDamagesReportNew

The new SQL views that I need to link to are identical in structure and content and on the same SQL server but different database - forkdw and are called e.g vw_R_Damages

Is there a straightforward process to do this without affecting the queries and reports in my Access db?

View 2 Replies View Related

Tables :: Text Field On Linked Table

Nov 12, 2014

We have a database (Access 2007) with several linked tables to an MS-SQL 2008 instance. All the text fields that I have issue with are nvarchar(255) on MS-SQL. The odd thing it will not allow a full 255 characters to be entered into the field. It will fail to save unless the number of characters is about 238 or 239 characters (not sure of the exact number of characters). It shows the field as a text and field size of 255 in Access .

View 2 Replies View Related

Tables :: Locate DSN File For Linked Table

Feb 18, 2014

I have a table that was linked from a SQL Server with a file DSN. It was linked years ago so now I want to locate that specific DSN file for that specific table if possible.

View 3 Replies View Related

Tables :: Sharepoint Linked Table Uneditable

Aug 27, 2013

I'm just starting out to learn SharePoint, which I utterly hate thus far but anyway. So I have successfully linked to the sharepoint list in my DB, but I cannot edit any values for some reason. I can edit them in sharepoint and both systems update, but when I type in ANY box in access I get "Invalid arguement" error.... also note the new record button near the record navigators is greyed out so I can't add a record either.

I noticed that my library user group has URL... set to Read while all others are set to full control... is this the cause or something else?

View 1 Replies View Related

Tables :: SQL Creates Linked Table In Front End

Dec 28, 2014

I have a frontend and a backend database. As usual i keep the tables in the backend. Now I have some tables which need to be emptied and then refilled. I empty the table using the code

Code:
DoCmd.RunSQL "DELETE * FROM TransNMTbl;"

then I fill in the table using

Code:
DoCmd.RunSQL "INSERT INTO TransNMTbl SELECT * FROM TransTbl;"

The code runs fine. The problem is that the linked table is not used and the sql statement creates another TransNMTbl table in the front end and puts the data there. Bu I need the table to be in backend.

View 7 Replies View Related

Tables :: Show Total Of Linked Table

Feb 22, 2013

Access 2010 ... I have 2 tables. One with base information second one is linked with multiple results each having a price. On table one i see the + sign when i click i can see the linked second table. Can i get a total amount of the price on table 2 on table one?

IE:
Table 1:
Trans ID - Seller - Quantity - Lot Cost - Parts Cost<-- the one i need total for.
123 - joe - 3 - $100 - $20 <-- the total of the 2 linked parts (Keyboard, Mouse)

Table 2:
ID - Trans ID - Part - Cost
1 - 123 - Keyboard - $10
2 - 123 - Mouse - $10

View 11 Replies View Related

Tables :: Appending Data To A Linked Table?

Feb 6, 2014

I have a table in MS Access 2010 that is a link to an external data file in .csv format. I assumed that it was not possible to append data to a linked table until recently. I appended 3 records to the linked table and discovered that the 3 records were appended to the table and the external data file it was linked to.

What am I missing ?

View 8 Replies View Related

One Primary Key In One Table Linked To Two Foreign Keys In Two Different Tables!!!

Sep 22, 2007

Hi there,

I am trying to model my database to create relationships between different tables in my database. During the process of doing so I ended up having one primary key in table A linked with to foreign keys in two different tables ( table B & table C) and both of those two table are junction tables to break out many to many between many other tables

Now for some reason!!! I feel that there is something wrong with my logic mapping and modeling of the relationships between tables due the fact of having one primary key linked to two foreign keys in two different tables :(

Is my intuition is right? should such case be considered as indicative of wrong modeling of relationships between tables in a single database
And if so what is the disadvantages of that link (talking down the road) when the database if fully populated? :confused:

By the way I am new member and new to database, so please take it easy on me :o

Many thanks

View 2 Replies View Related

Tables :: Finding Specific Record In A Linked Table

Jun 19, 2013

From what I have read, I understand you can't use the seek command on a recordset from a linked table from another database. Is that true? If so, what is the alternative to find a specific record in the table using an indexed field?

View 4 Replies View Related

Tables :: Linked Table Disable Password SQL Server

Feb 4, 2015

In Access2010 I have created a linked table to a SQLServerExpress database.I have a user in SQLserverExpress with only reading rights.This linked table is used in a form which only shows the information. When opening the form, it prompts for the SQLserverExpress username and password.How can I configure the linked table so that it logs in automatically with the SQL user I prefer so that the user does not need to enter a user / password?

View 3 Replies View Related







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