I have a need to copy a table structure from an existing linked table in the backend
i have done this by
a) copying the table structure into a new table in the fromt ebd
b) exporting the new table into the backend
c) dropping the table in the front end
just wondered if there was an easy way to do this directly in the backend?
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?
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.
I have a client that is using a split database. I am working on an update to the program and need to transfer a table to the backend that has the correct structure and information included in it. My thoughts are to make a one time use program that transfers the table to the backend. I have seen DoCmd.TransferDatabase and DoCmd.CopyObject as possible ways to go.
View 10 Replies View RelatedHow can I overwrite the existing Access database with another Access database without the need for the end user to get involved in the process.
I want the code to copy a new mdb file over the database I am using (!) and then automatically start the new mdb file...
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
I have a table with employees’ information. I want to copy all records in this table to another existing table “WorkTimes”. I do not want to edit my employee table, so therefore I want to copy all records to another table before editing/adding information in other fields. How I can do that with a macro or module?
Thanks in advance.
I have an Access database with linked tables from Sybase SQL Anywhere, via an ODBC data source. I have just linked the tables recently, all the data used to be in native Access tables. It is fairly common for members of our team to open these tables in datasheet view, and copy/paste several records to create new records, then edit a few fields on the new records. This used to work fine with native Access tables, but when we try it with the linked tables we either get an ODBC error - "primary key value already exists" or the new records show up with #Deleted.
The root of the problem is this: In the old Access tables, the primary key was an autonumber field, and Access was smart enough to assign new ID's when you copy/pasted records. In the linked table, the primary key is type "Number" in Access, and Access is not smart enough to let Sybase assign new ID's when you copy/paste records in datasheet view. Access is trying to force the existing ID's into the primary key field, and Sybase says "too bad so sad".
Of course, I could just write some quick append queries to copy/paste the data. And in the short term, that's exactly what I will have to do. But is there any way, long term, to allow members of my team to do it the "quick & dirty" way by copy/pasting in datasheet view? I have tried to change the primary key to an autonumber field in design view, but Access doesn't allow that. Is there a way to do it in code, or a way to force Access to allow Sybase to always handle the primary key field?
I have the usual setup with a front and back end.
I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself.
I'm trying to create a button that will open a save window so that user can select where and the name of a new backend file.
and create a new backend file from the one in use and link to the new open.
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).
i understand the necessity of foreign keys to link tables and create dynamic data structures..
believe it or not i have a degree in this subject, but am finding one little issue quite confusing...i shall illustrate this with an example...
THE RIGHT WAY TO DO SOMETHING - 2 tables.. table1(DRIVERS) table2 (JOBS)
DRIVERS
driverId
name
phone
JOBS
jobId
date
driverId
each job is carried out by one driver, and drivers can do many jobs throughout their lives...obviously. i learnt that in good practice, the above two fields should be used to link these 2 kinds of tables together..
to my knowledge.. to link the two tables like this in access.. i would view the 'Relationships' and drag one field to the next.. so the little line joins them and everyone is happy..
HOWEVER, i have created this database and have used this method...imagine my user adding a job to the database.. all i want them to do is choose the drivers name from a dropdown menu for each job. to acheive this i have used the 'lookup wizard'... this successfully associates the chosen driver to that job and when you query the database, the results are valid.
this link i have created has nothing to do with the Ids as illustrated in the above example. what i need to know is if i link the two tables in the 'proper' way using the Ids.. how can i allow my user to choose the drivers name when entering a job.. rather then choosing the drivers Id number on the form because that would be confusing...
i hope this post wasnt confusing..
am i mad...someone please explain to me???
I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?
View 2 Replies View RelatedI have recently asked about being able to use my table system online. i have just thought. what about creating a front end that works online. still have the front end on the desktop and the table system on the desktop but have both of them connecting to the one table system.
View 8 Replies View RelatedI am working with Access 2007. I have a pretty good working knowledge of access. Here is my problem. I have a warehouse which supports four departments. The warehouse floor area is segragated into four areas. Area 1 department 1, area 2 department 2, and so on. Each department uses the same commodity, so I will have four of each item, belonging to each department. The storage locations will be unique and not repeated anywhere in any department. I receive and issue items for all departments.
The problem is how do I structure the tables and queries to receive an item for department 1 adding it to department 1 only and not have the item added to another department with the same item. Also to issue the item from a department 1 without effecting the on hand balance of any other department. I am too the point of creating 4 separate databases one for each department.
Hi Guys!
How would I make a copy of my Database, but only copy the structure and not the data.
I know that you can do this with tables (Copy, Paste > Structure Only), but can you do it with the whole DB?
Cheers!
If i make minor changes in the backend db structure (eg add a feild, change properties of a feild in a table,). These changes are not reflected in the frontend. how can thses changes be reflected in the frontend usig the simpest method.
View 2 Replies View RelatedHi all,
I've set up security on my database before with great success, and now I'd like to use the same workgroup file (a copy with a new name and location) to secure a nes db.
I open the new db, and join my earlier created mdw file. I'm prompted with that I have joined my workgroupfile.
I then set permissions for each user in the workgroupfile, remove admin rights for the admin user account, and assign admin rights to my own account. I am also the owner of thr db.
I now run the security wizard, and I make no changes to any checkboxes.
This goes nicely, and the wizard closes after making a backup file.
I now want to re-join the system.mdw file, so I don't get prompted with the logon dialog when opening a new db or an unsecured db.
If I now try to start my new secured db by double-clicking on the secured mdb file it opens ?????
Where am I going wrong?
Thanks in advance for any help.
I have a database and I wish to be able to copy 10 out of about 20 fields into a new record automatically using a command button on a form. Similar to the way the Duplicate button or the Create New button would work but only with these selected fields. None of the fields I wish to duplicate are the primary key. The primary key is an AutoNumber field. After clicking the Save button, the primary key would increment one to confirm the save record.
Any help or suggestion would be appreciated!
Thanks!
We have a program in Access that makes invoices. I am interested in copying one of the invoices to be used as a new invoice..How do I add a 'copy' button?
View 6 Replies View RelatedSometimes extensive updates have to be made to a table using an update query or loop through a record set.
In order to be able to undo changes in case of a mistake I am thinking about the following procedure:
In code:
1 - Make a copy of the table
2 - Make the changes in the original
3 - Check if updates are correct and if so: delete the copy and if not:
delete the default table and rename the copy so it will be the original.
Is that common practice or not?
In one table (Alpha1) I have a person's general information (e.g., name, SSN, address, phone number, etc.).
I have a person enter his or her SSN and the general information is populated.
I want the person to click on an icon and all the Alpha1 information is transferred to a new table (same field names), then bring up that same record in a new form.
I'll do the work. I simply need the general concept of making this work.
I am trying to copy a table and paste as another name. I do this all the time with this table as it is a process I have to do. It is giving me a -1524 error. I also tried to export it to another database and it won't allow that either. I get the same error.
I have tried this with other tables and it does fine.
Is there a copy and replace existing records function in access?
I.e. I would want to copy records from one table to another (with same structure) and replace similar records with in original table in the new records (which have minor ammendments made).
I have a table customers, which i can select from an combobox. In my form two tables are bound. one is the customers, and the other is a table where i want to copy the customer information.
I tried this code to copy the selection I made in the combobox afterupdate.
[T_Invoice_firstname] = [T_customer.firstname]
But I get an error 2448 saying I cant assign a value to this object.
I tried msgbox [T_customer.firstname] and i see the name
I tried msgbox [T_invoice.firstname] and i see the empty record
I even filled in a name in the t_invoice.firstname and it shows with the msgbox also.
So I have clearly doing something wrong here.