Rats! - "select...into" Replaced Linked Table

Oct 10, 2007

Hello folks,

I am looking over a mdb that I recently took over and found a problem.

There is a query that does a "select...into" a table, which obviously replaces the original table with the new records from the select statement.

Ok, but... The table is supposed to remain a linked table. The query currently replaces the linked table with a local table viewable only by the locally running mdb.

QUESTION: Is there a way to set up a select into statement to replace a linked table with another linked table?

I found:

dim mydb as database
set mydb=currentdb
mydb.execute ("INSERT INTO Table1( [Field1], Field2, [Field3] )
SELECT [Field1], Field2, [Field3] FROM Tablename IN 'c:foldernamedatabasename.mdb")

Which could do the trick, but I would prefer not to hardcode the path to the network data.mdb. Maybe I could query the path of the table and use that into the above... Ideas? Just now thought of that...

I figured I would ask before I rewrote the query to merely update with new records and then delete old... or delete all then update/insert with all new...

Ideas?

Thanks!

View Replies


ADVERTISEMENT

Reports :: Select Most Recent Record From A Table That Is Linked To Another Table

Jul 13, 2014

I have a basic database design, well I think so anyway. It only has two tables:

1. tblClientInfo
2. tblNotes

Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.

I then have two forms:

1. Claims Loss Form
2. tblNotes_DatasheetSub1

So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).

Example of a print out of what it is doing:

John Doe - 123 Easy St - Notes 1 (Most Recent Note)
John Doe - 123 Easy St - Notes 2 (Previous Note)
John Doe - 123 Easy St - Notes 3 (and so on...)

It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.

How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?

I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.

I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.

The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.

View 2 Replies View Related

Text In Memo Field Is Being Replaced With # Signs

Mar 10, 2006

Hi,

I have been searching and searching and cannot find any info on this specific problem.

I have a subform that users go into and enter data into the fields. Intermittenly, when a user closes out and then goes back in, the data in one of the fields (always the same field) is missing and has been replaced with ########.

Any insight or advice would be greatly appreciated!!

Thanks,

Krysti

View 1 Replies View Related

Combo Box Based On Linked Table Needs To Pull Info For Sub-Form From Non Linked Table

Aug 31, 2007

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

Thanks in advance for ANY and ALL help that I get from here

View 10 Replies View Related

Characaters Replaced By Vertical Lines/empty Boxes

Sep 12, 2006

I have a very simple database with one big table and a series of forms to walk researchers through data extraction from scholarly articles. Each researcher has a copy of the database and I export their info and merge it in a master file. One of my new researchers has just started and come across a problem I've never seen before. On her third record, access suddenly started replacing characters typed in (or selected from drop-down boxes) to the form fields with bold vertical lines, like this: ||||||On the table, they look like empty boxes instead of vertical lines. What she says happened was:-She was entering data into one of the forms (say form 4 of 7 - there are macros to open the new form when you finish the one you've been doing, but they only all close at the end of all 7 forms)-She saved-She looked at a PDF document on the same computer-She switched back to the database and several (but not all) of the characters on the form were replaced with the vertical lines. When she looked further, some fields on other forms (but not all the other forms) also had these vertical lines. Finally, one field had replaced the characters she'd typed not with vertical lines, but with some characters from another field. Is her copy of the db just corrupted? Is this some known bug? Can we avoid this? Thanks for any insight or referral.

View 1 Replies View Related

Query Linked To Combo Box... Select All?

Sep 1, 2005

Hiya,
I currently have a query that looks up staff names and then if they click a staff name from the combo box on a form they are then able to preview a report showing their performance. I want the combo box to have the ability to be left blank or the option to select all so that I can preview all their performance in the report.

How do i go about doing this?

Andy

View 1 Replies View Related

Linked Table -- Database Name Linked To Table

Oct 4, 2005

Hi,

I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.

I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.

If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.

I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.

Can anyone point me in the right direction??

Thanks

View 4 Replies View Related

General :: Copy Data From Local Access Table To Linked SQL Server Table?

Jan 13, 2014

I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple

Code:

DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"

This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).

I would like to copy the data in a bulk operation, or operations that I can execute programmatically.

View 1 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

Extracting Data From Linked Db2 Table Using Access Make Table Query

Aug 29, 2006

Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?

Thanks for your support
GinnyP

View 1 Replies View Related

General :: Possible To Have Data From Linked Table Automatically Update Into Existing Table?

Aug 17, 2012

I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.

There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.

Is it possible to have the data from the linked table automatically update into the existing table?

View 1 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

Table With Country, ShipCountry, BillToCountry Linked To One Coutries Table

Nov 13, 2007

I would like suggestions on how to "properly" set up the tables and relationships required to do the following.
We have a customers table that can contain a Country, ShipCountry, and BillToCountry. All 3 countries could in theory be different. I want all 3 to store the CountryID from the Country lookup. I am trying top avoid a circular reference or any data contention issues. What is the proper method to do this?
Thanks very much in advance!

View 4 Replies View Related

Append Records From An Access Table To SQL Server Linked Table

Jul 21, 2006

I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.

The following error message occurs:

"ODBC -- insert on a linked table 'linked tblname' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)

I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.

What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.

I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)

View 1 Replies View Related

Forms :: Showing Data From Linked Table And Local Table

Nov 21, 2013

I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.

Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".

View 2 Replies View Related

General :: How To Cleanly Convert A Linked Table To Local Table

Apr 1, 2015

I have a database that is designed with a front and back end, with the FE linked to the BE. Some potential customers want to play around with the database, and the easiest way for me to get them access to it would be for me to combine the FE and BE and just give them a copy of that. how to link a table, and I know how to import a table. What I'm unsure of is how to cleanly convert a linked table to a local table.

View 2 Replies View Related

Access Error 3027 When Using VBA To Append Table To A Linked Table?

Apr 7, 2014

I have two tables, submit and imgdest. Submit is edited by front-end users to load pictures for back-end users who then delete the images when they're done with them. Submit is edited by a form, in the form I've placed a button (Command37) that has code:

Code:
Private Sub Command37_Click()
Call InsertData
MsgBox "Completed", "0", "Completed Backup"
Exit Sub
End Sub
Private Sub InsertData()

[code]....

This code was working for a short while, now anytime it's run I get error 3027 - Cannot update. Database or object is read-only. However, I can open the linked table and manually change information in it with no problems.

View 14 Replies View Related

Local Table And ODBC Linked Table Don't Have Same Filter Options

Dec 31, 2014

In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.

My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.

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

Forms :: Update Table From Text Box Already Linked To Another Table

Dec 13, 2013

I have a form that contains the following: Combobox, (Lists BadgeNum from tblPersonnel)

2 Textboxes (LastName, FirstName) populated via code from the combobox using info from the same tblPersonnel.
2 labels (one containing Date, another containing Time)
ToggleOnButton (Valueof 1)
ToggleOffButton (Value of 0)
Savebutton

This form is basically used as a cheap police timeclock. All Im trying to do is when a user chooses their name from the combobox, clicks ON or OFF and then save, is just write the much of the same info to a table. Specifically, BadgeNum, DateIn, TimeIn, DateOut, TimeOut.

View 4 Replies View Related

Copy Linked Table Into A Local Table

Jul 30, 2006

Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.

The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).

View 2 Replies View Related

General :: Linked Table Manager Looking For Old Table Name

Jun 20, 2014

I have a database that is used in our office. It is split with the backend stored on a network share.

I need to make some updates, and to do maintenance I usually make a copy of the frontend/backend to my desktop, and use the linked table manager to switch to using the local copy of the backend so I don't mess up the main data. All good so far.

however, I recently added a new table, and originally called it "overRides", I then decided that "adjustments" was a more suitable name and changed it.

Now whenever I try to switch from the main backend to my local copy I get a message when relinking "adjustments" that Access cannot find the object "overRides" and to make sure it exists and the name is spelled correctly...

I have been just deleting the linked table and reimporting "adjustments" but this issue keeps coming back and it's driving me nuts!

How can I make Access forget that this table used to be called "overRides"?

View 6 Replies View Related

Insert Records Into Table Based On The Select Criteria From The First Table.

Mar 22, 2005

Hi

I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).

Forms involved are frmmain and frmsub.

Frmmain contains the call table information in the main form and parents information in the subform.

When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.

Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”

View 4 Replies View Related

Linked Table Look Up ??? HELP.

Jan 29, 2005

OK, so now in my Dbase Ive got the main data, and another table with 2 fields in,

in the second table its a part number and a weight,
on the main data I need the user to select the Part number with a combobox that the weight is filled in the maindata automatically, I dont understand Relational Dbases, yet so if anyone knows how I can do this, please explain in little words, lol


I did manage to do this earlier in the dbase with box sizes and weights but as there are only 4 size of box I coded it into he VB, but with the caps there could be up to 100 different caps.

Anyone HELP PLZ.

View 4 Replies View Related

Which Table Is Linked?

Apr 23, 2006

I'm working with a split database. I frequently change the b/e link to different .mdb files. Sometimes it's because I'm debugging or testing changes to the front end, sometimes it's because I'm working with data from different projects.

I thought it would be nice if I could display the name of the currently-linked back-end .mdb file on a form. I've been unable to find a way to return to a variable the name of the b/e file.

Any suggestions?

I'm using Access 97 (still) on Windows XP Pro.

View 2 Replies View Related

Linked Table From VB App

Aug 24, 2007

I have an access database that a VB program links into to a specific table. There are dozens of people linking into this database. Since its separate VB program that links in i cant use auto logo off to do database maintenance. What can i do to create a workaround ? Is there a way in Access to force close links to table?

View 1 Replies View Related







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