Split Database Make-Table Query Problem
Mar 30, 2007
Hi All,
I've developed a database that was working fine until I split it to use in a network enviroment. The problem is a make-table query that now make it's table in the front end instead of the back-end. I need to run a make-table query because the query uses a custom function which then causes errors when the query is accessed by a web form that I use.
The only way I thought of getting round it was to create a permanent table in the back-end which is cleared every time and an append query used to populate it.
Anybody any other ideas?
Cheers
View Replies
ADVERTISEMENT
Jun 12, 2012
So a while back I created a database which I use to keep track of my companies large list of products. It is very simple only 3 tables, 1 query and 1 form. After running into a problem with copying and pasting updates on each computer in the office I decided to split the database on a network drive. This worked for a bit, however I had to add new fields and modify the form, which corrupted the file. Luckily I had a backup before the split.
As far as I could tell all I had to do was modify the backend file and make a new front end. However it seems like it is not as easy as I thought it would be. How do you modify a split database without corrupting the files or using a non-split copy?
View 1 Replies
View Related
Sep 14, 2012
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
View 2 Replies
View Related
Jul 6, 2015
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
View 3 Replies
View Related
Jan 27, 2006
Hi all,
I am trying to update a table in a database (Lets call that DbNew). I want that when DbNew form is loaded it automatically updates its underlying table (lets call it TblNew) by running a make table query (called "coversheet") that is located in another database (call that DbOld). DbNew and dbOld are all on a network and I dont have total access on DbOld (but I have created the make table query "coversheet in DbOld).
I should also state that DbNew is bound to TblNew and I am not interested in keeping the old table in DbNew. Information is frequently updated in DbOld and all I am interested in is loading the most current info in DbNew so the current table can be deleted upon loading DbNew and a new one created. Here is what I tried doing and I have it in the form load event of DbNew. This however returns an error (Runtime Error 3010. That TblNew already exists)
Dim db As Database
Set db = OpenDatabase("\networkdbOld.mdb")
db.Execute ("Coversheet")
DoEvents
Set db = Nothing
End Sub
P/S: I am not good at access in anyway I should state.
Thank you for taking the time to read.
View 8 Replies
View Related
Feb 12, 2012
I am using Access 2003.I have created a Makle Table Query which gives me the invalid argument error because it makes the database balloon to over 2gb. I have two questions:
1. Is it possible to get the make table query to save data in one database until it reaches say 1.9 gb and then begin saving the rest of the data in another database?
2. Is there a wey to pause or stop a make table query to see the results and then let it run again?
View 4 Replies
View Related
Aug 18, 2014
So I split my database into a front and back end, now i wanted to add a table to the database. I created the table in the backend but I am not sure how to make it appear in the front end. I am using access 2010. I tried the link table manager but the table I created doesn't appear there.
View 4 Replies
View Related
Aug 18, 2014
So i split my database into a front and back end, now i wanted to add a table to the database. I created the table in the backend but im not sure how to make it appear in the front end. Im using access 2010. I tried the link table manager but the table i created dosent appear there.
View 2 Replies
View Related
Sep 17, 2014
My db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
View 3 Replies
View Related
Mar 24, 2014
I'm having a problem with a database that i had split. While creating teh database, i had it stored on my personal drive at work, but wanted to move it to the shared drive so the data could be stored there. this is to deal with cross site network connectivity issues that we encountered with another database here at work.
When splitting the database, i split it on my personal drive and then dragged the back end to the common drive. I realized my error, but when attempting to correct it, i somehow have two of the same file, and no back end?
I receive the error on the attached document when attempting to access any of the tables or information in either of the files, the "back end" that i placed on teh drive, and the "front end" that i have on my personal drive.
I also tried to relink the tables through the linked table manager, but receive the same error when attempting that.
Is there a way to recover from this? I had backed up my file by making a copy prior to splitting, but somewhere in this process, this became linked to these and is having the same issue. Is there a way to recover the old "unsplit" version? I cannot copy over all of teh tables, etc. because i get the same error as earlier.
I can attach a copy of my database for reference...
View 5 Replies
View Related
Apr 8, 2015
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 Related
May 27, 2014
I have a split database made in Access 2007. Each user gets their own copy of the frontend from a script. I wanted to be able to edit the design view of the backend tables even if people were using the database so I made all the forms use snapshot source and only allowed data updates through VBA macro update queries. Having any form open locks the backend source table from being edited. In fact, I've found that just having a normal snapshot query open causes the message "Either an object bound to table 'whatever' is open or another user has the table open. Do you want to open the table as read-only?"
Is there some way to have a table be the source for a form or query, but still have it designable under most circumstances?
Attempted to late-bind a recordset on form load; result was the same:
Code:
Set rs = CurrentDb.OpenRecordset("Select redacted as ft from tblRedacted ", dbOpenSnapshot, dbReadOnly)
Set Me.Recordset = rs
Set rs = Nothing
View 3 Replies
View Related
Sep 22, 2013
I have split the data base
Added a new table to the back end .But do not know how to update it to the front end
View 4 Replies
View Related
Mar 13, 2008
I have a time reporting database called Productivity. I also have a split version with ProductivityFE containing the forms, queries, and reports and ProductivityTables containing the data. All three are on my PC. Each month I update Productivity with new records and run a report or two to check things out. Then from ProductivityTables I import the data tables. If there are any updates to the forms, queries, and reports these are imported into ProductivityFE. I again test a couple of reports before finally placing both FE and Tables on a server. The managers then place a copy of FE (from the server) onto their desktop and run reports from there. So far so good until this month when I created a new query for a new report. It runs fine in Productivity, but in the split database (on my PC) I get the error 'Invalid Bracketing of name [tblCostcenters.ProdCenter]'.
Below is the SQl from the query. Of note here is a time record contains both a home costcenter and a worked costcenter for the employee. I have two instances of tblCostcenters in order to reference the field ProdCenter for both the home and worked costcenters. The code fails at the first IIF statement on [tblCostcenters.ProdCenter]. Based on other posts on this sight I tried replacing these brackets with [tblCostcenters].[ProdCenter] and [tblCostcenters_1].[ProdCenter] and it seemed to be working until the last bracketing was changed, after which I was faced with the parameter query dialog box looking for a value for tblCostcenters.ProdCenter.
So I'm stumped. Why would the query work in a single database but not in a split database?
SELECT tbleTimeData.HCCtr, tblCostcenters.Title AS HTitle, tblCostcenters.ProdCenter AS HProdCenter, tbleTimeData.WCCtr, tblCostcenters_1.Title AS WTitle, IIf([WCCtr]="","Other Affiliates",IIf([tblCostcenters.ProdCenter]<>[tblCostcenters_1.ProdCenter],"Outside Product Center",IIf(IsNull([tblCostcenters_1.ProdCenter]),"Outside Product Center","Within Product Center"))) AS WProdCenter, Sum(tbleTimeData.Hours) AS SumOfHours
FROM (tbleTimeData LEFT JOIN tblCostcenters ON tbleTimeData.HCCtr = tblCostcenters.Costcenter) LEFT JOIN tblCostcenters AS tblCostcenters_1 ON tbleTimeData.WCCtr = tblCostcenters_1.Costcenter
WHERE (((tbleTimeData.FY)=[Forms]![frmMainMenu]![cboFY]) AND ((tbleTimeData.FP) Between IIf(IsNull([Forms]![frmMainMenu]![cboFPFrom]),[Forms]![frmMainMenu]![cboFPTo],[Forms]![frmMainMenu]![cboFPFrom]) And IIf(IsNull([Forms]![frmMainMenu]![cboFPTo]),[Forms]![frmMainMenu]![cboFPFrom],[Forms]![frmMainMenu]![cboFPTo])))
GROUP BY tbleTimeData.HCCtr, tblCostcenters.Title, tblCostcenters.ProdCenter, tbleTimeData.WCCtr, tblCostcenters_1.Title, IIf([WCCtr]="","Other Affiliates",IIf([tblCostcenters.ProdCenter]<>[tblCostcenters_1.ProdCenter],"Outside Product Center",IIf(IsNull([tblCostcenters_1.ProdCenter]),"Outside Product Center","Within Product Center")))
HAVING (((tbleTimeData.HCCtr) Between IIf(IsNull([Forms]![frmMainMenu]![cboCCtrFrom]),[Forms]![frmMainMenu]![cboCCtrTo],[Forms]![frmMainMenu]![cboCCtrFrom]) And IIf(IsNull([Forms]![frmMainMenu]![cboCCtrTo]),[Forms]![frmMainMenu]![cboCCtrFrom],[Forms]![frmMainMenu]![cboCCtrTo])) AND ((tbleTimeData.WCCtr)<>[HCCtr]));
Thanks!
John
View 2 Replies
View Related
Sep 28, 2012
I linked table rawdata from a database called competitor from a ODBC Database. I run a query with to make a table with a criteria where it retrieves roughly 10 columns with 719,000 rows. And it gives me this error.
(Cannot open database". It may not be a database that your application recognizes, or the file may be corrupt.
I asked the creator of the database and they said the database grows automatically and it was created with the same Access version as i am using to run the query.
Also when I do a RUN without creating a table and just selecting and displaying the data it does not give me any issues.
View 4 Replies
View Related
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
Jan 21, 2005
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
View 1 Replies
View Related
Sep 23, 2007
Dear Access Expert
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
View 6 Replies
View Related
Oct 14, 2013
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies
View Related
Dec 22, 2005
When using a make table query, can you include an ID tag. in the output table? if so how
View 2 Replies
View Related
May 15, 2006
:confused: I am using a Make Table Query to filter a Linked Excel Table. Is there a way to cut/drop the first 8 characters of the text out of one of the fields as it creates the new table?
Field NameExcel DataFinal Data
Model_NameLATITUDE D600D600
Also, the final table has two Relationships with two other tables. When I run the Make Table Query once a week, I have to break the relationship to get it to run. Is there an easier way to dial with this?
View 2 Replies
View Related
May 19, 2006
Is there a way in a Make-Table query to tell the table to open when it's created? I would assume there is a function or SQL code that would do this, but I know very little SQL. Can anyone point me in the right direction? Thanks!
View 2 Replies
View Related
Aug 27, 2007
I have a database named NewUpdate.mdb. I have another database named MainDatabase.mdb. Almost all the tables in NewUpdate.mdb are linked tables that are in the MainDatabase.mdb file.
I've written a simple make-table query in NewUpdate.mdb that makes a backup of a table that is located in the MainDatabase.mdb file. (See code below.)
SELECT tblProviderRate.* INTO tblProviderRateSave
FROM tblProviderRate;
The only problem is that this new table is created in the NewUpdate.mdb file. I need the query to be stored in the NewUpdate.mdb file and the "new" table to be created/stored in the MainDatabase.mdb file.
This sound simple enough, but I'm drawing a blank as to what I need to do in order for this to occur. Can someone tell me what I'm leaving out??
Thanks,
CRhodus
View 2 Replies
View Related
Nov 26, 2007
How to make-table query to make a certain field a memo field instead of a text field? The reason I cannot use the text field is the limitation is 255 characters while I require 2000 characters?
View 4 Replies
View Related
May 28, 2006
Dear friend
I need your help desperatly. In my db, I am making a "make table query" to match some text.
Help me build this please.
First a form which has text box, where I will put in some words..
Then a make table query, which will have =[form]![name of form]![control name]
A report on same table.
Means when some one puts some words in text field of form, on pressing hit button, a like statement is automatically created in one field of query which matches those words and a table is generated from them. Result is report.
Hope this is clear, could you please help me build this?
View 14 Replies
View Related
Dec 7, 2006
How can I set a button in an MSExcel sheet to run MSAccess make table query?
Please help. Thanks.
View 2 Replies
View Related
