I have a database that acts as a front end for two different back-end databases. What I want to do is be able to copy a table from back-end #1 into back-end #2.
My first stab was to try to do a TransferDatabase on the linked table from b-e #1, but that just put a link in b-e #2, and not the actual data.
Can this be done?
Thanks,
David
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 have a back-end db in access 2010, linked to a set of sharepoint tables on office365.
I want to make a completely unlinked copy of the db to store a "snapshot" of the data on a fixed date.
I have tried "save as" using the "Save as Local Database" option, but for some reason, it still saves as linked tables.
How to go about it?
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
View 5 Replies View RelatedHello. I have created a database to keep track of clients and their training requests. I have already created the tables and have some data entered. It is just now that when attempting to create a query with the two tables that I realized I have problems as I am getting a Type Mismatch error.
The first table keeps track of clients. Each client has a unique client number field which is an AutoNumber. The second table is a Training Request table that keeps track of the training requests for each client. Each client may have many training requests. I entered a Client Number field in this table too and that is how the two are linked by relationship. I set the Client Number field in the Training Requests table to be a Text field. I realize they are different data types but I didn't think I could set it to be an AutoNumber in the Training Requests table.
How can I fix this table design? Would I just be able to change a data type? I am very unsure of what route to follow.
Thank you in advance for any help that can be provided.
I am fiddling with a DB right now just to try to work out some table relationships. I have a PK, Name, it is accompanied by a year in school. I then want to relate the year in school in a different table with the requirements for that year.
such as
Year Requirement1 requirement2 requirement 3
these would be check boxes (yes/no), and I could accomplish it in this format, but I am trying to have a normalized DB so
I realize it should be like this
1st year requirement
1st year requirement
1st year requirement
but I do not know how to link this back to the PK name when switching it to this format, so that I can pull up a form that shows if the person has completed the requirements for the year they are in. I am I going about this wrong? I have been looking at normalizing DBs and relationships but can't figure out the logic for my DB thought.
Thanks for any help
Riley
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 form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
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 created my database a few months ago and split the database into a front and back end. I recently added a new table on the front end. How can I move this table to the back end and re-link it?
View 3 Replies View Relatedmy problem is that when i make a booking on my booking page it all saves, but when i go to the table where the bookings should all be kept, they all dissappear and only the recent booking i have made shows.
View 10 Replies View RelatedMy 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.
I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.
Any help or examples are very much appreciated.
**EDIT**
I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.
Thanks a Bunch!
EXAMPLE**
Current Loans (Table 1):
CustomerID
Name
Address
City
State
Phone
Customer History (Table 2):
CustomerID
Name
Phone
That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.
I have a Front End Database and a Back End Database.
Certain tables in the BE are linked to the FE.
Once or twice a day I need to run Make Table Queries in the FE, but I am unable to do so if someone else has the BE open.
Best solution would be greatly appreciated
Many thanks
John
I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.
Would appreciate any help.
Can I create a table in the back end of my database using VBA in the front end? My client is using this system and I can't easily go to their location. I need to create a table on the back end and have it available in the front end applications. My thought is to create a front end only for the purpose of updating the backend with this new table. My idea is now to have it open with a button for the user to click - this will add the table to the backend with the required fields, confirm the update was completed and then close. This app then would be trashed so it would not be run again. (Though I would put a check in the VBA that if the table existed, the user would get a message box and the coding stopped to avoid overwriting the existing file.)
And once the table is created, is there a way to programmatically link it to the front end. I will be providing a new front end with this update so I could add something in the new version.
I was trying to do this in a SQL Q but thought it would be easier to do multiple calculations in my form if I used Calculated Controls instead.
I have a table Lookup_Tbl_Fiscal_Year with Start_Date, End_Date and FSA_FY.
In my form I want to take the date entered in Last_Signature_Date and look up the FSA_FY.
The Last_Signature_Date would fall somewhere between the Start & End dates.
Here is what I got but no matter what date I enter I get 0 back.
=Nz(DLookUp "[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Last_Signature_Date]">="[Start_Date]" And "[Last_Signature_Date]"<="[End_Date]"),0)
I read that this might slow down the system. Is it better to use SQL?
I did a search in here as well as Google to figure out why when I relinked the front end to the back end after adding 2 tables to the back end that the tables did NOT show up on the front end. Only the original tables that I began with.From what I have read there are different ways to do it. One suggestion on here was to create the table on the front end and export it to the back end. I guess I will have to do this going forward.
But my question is, after searching pages upon pages with no luck, since I already have the tables in the back end how do I tell the front end to find them and link them?
I have a user who wants to automate copying a table from our main database. Basically, he is naming the new table with the original table name and the current date. For example, Part_Table_9-3-04, Part_Table_9-4-04, Part_Table_9-5-04 etc for each day of the month.
He wants me to write a macro, module, or vb code that automates the steps.
I have DoCmd.CopyObject,"Table1",AcTable,"Table2" I want to concatenate the date function (now) or (today) with the new table name but can't seem to get this to work.
Any suggestions?
Thanks for helping,
Jeff
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.
How to copy a column from one table and insert it into another table in the same database
Hi, All,
I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.
To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.
Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.
I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.
What is the best way to proceed?
Thanks for your help.
Adrian
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 have a problem... I have four tables(but my problem is limited to just two)
The tables are Orders, Customers, Items and OrderedItems
The two more detailed below creates the problem when trying to copy an already existing order to a new since the customers usally order the samethings over and over again.
Orders
IDOrders
IDCustomers
strSalesperson
strWhen
strSent
OrderedItems
IDOrderedItems
IDOrders
IDItems
iNumber
bPacked
So I have made a copy button on the order form. The copy button should do this:
1. First save the old orders IDOrders and IDCustomers (which it does)
2. Create a new order with old data (which doesnt do)
3. Copy all post in OrderedItems with a new IDOrdered using INSERT INTO and using columns
My SQL statement is like follows:
sSQL ="INSERT INTO Orders(IDCustomers, strSalesperson, strWhen, strSent, strComment) VALUES (" & itmpIDCustomers & ",'" & strSalesperson & "', '" & strWhen & "','" & strSent & "');"
But the reply I got is "Could not find output table 'Orders'" but I have checked the names over and over again but it doesnt work.
hey guys,
how can i make a query to copy records from one table to the other, and leave out one record. the record has corrupted somewhere and is causing havoc on some forms.
cheers
I've got a query that uses a key from one table to pull back records from another.
My question is...
On the table with the key there is another field that I want to use to pull back data from the same table from above.
A bit more info...
Table one holds colleagues details
Table two holds records
Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?
Schedule an import or export operation
I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on. Any query or any code that can pull back end table stats?
View 5 Replies View RelatedI am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.
For example:
Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:
Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#
The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?