Modules & VBA :: Can Split A Backend Into Two

Jul 1, 2013

I've got a Split Database (Front End & Back End). So far it was all good until recent when a department placed in a different city (200 Miles apart) wants to use my application. The Application has gone very slow for them when they access the back end.

Things which I've already done.
Analyzed Tables
Analyzed Performance

The only best way I could think of is to have two separate Back Ends for both cities and synchronize them may be every hour or so. Is this possible and how much feasible is this? Or is there an even better way to accomplish this.

View Replies


ADVERTISEMENT

Modules & VBA :: Split Database - Moving Table To Existing Backend

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

General :: Split Database - Modify Backend File And Make A New Front End

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

Modules & VBA :: Using SQLite As Backend

Nov 12, 2013

I am using SQLite as a backend as the speed/size is so much better. 400 Mbs down to 39 Mbs. Query time 14 seconds down to less than 1.

However what would be the best way to append data. Even though the tables are linked, a normal Append does not work, and a pass thru query does not work. Local data needs to be transferred to the SQLite backend, but a pass thru cannot deal with a local and remote table (apparently).Current solution is to dump the data to the backend as a table, then execute an insert query on the other remote table, then drop the uneeded table.

View 6 Replies View Related

Modules & VBA :: Backup Backend Using Batch File?

Aug 14, 2014

I've created some code to make a backup of my database backend. I've tested it and it seems to work fine, I just have some questions about it's use. Here's the code:

Code:
Public Sub backupBackend()
Dim TestFile As String
Dim strReplFile As String
Dim copyFromLocation As String
Dim copyToLocation As String
copyFromLocation = "S:Trust 2 AnalysisData"

[code]....

It will be triggered by a button push. It creates a batch file to do the copy operation and then delete itself.

In an ideal world I would schedule the backup at a safe time through the Windows scheduler, but our IT department won't allow me to do so.

The concern I have is that we could potentially press the backup button while a user is performing an action that writes/modifies data. If this were to happen would my backup potentially contain corrupted data?

If so, is the workaround to simply press the backup button when nobody is using the database?

View 3 Replies View Related

Modules & VBA :: Force Disconnect From A Backend Database

Nov 17, 2014

I have a database stored out on a shared drive at my company that multiple users connect to via a front end application that they save on their own hard drives. Recently I have noticed that the back end database keeps getting opened exclusively which prevents other users from being able to access the back-end database. For the life of me I cannot figure out what about the front end opens the back end exclusively and cannot find any plausible solutions I thought I could develop something that could force close a back-end database or disconnect all users from it?

View 3 Replies View Related

Modules & VBA :: Create New Backend File From Existing One

Nov 4, 2013

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.

View 4 Replies View Related

Modules & VBA :: Update Open Form After Linking To A New Backend Database

Mar 18, 2014

I have an Access DB with a form that allows the user to select a new backend database. I can connect to the backend and then .refreshlinks but nothing on the form is updated. I have tried requiring the form but that doesn't do anything. I've tried loads of other things, refresh, recalc etc., but nothing updates the open form.

The only way I have managed to get it to work is to close the form and reopen it, then it shows the data from the newly linked backend database.

While it works, it doesn't look good but also there seems to be some problem with it because eventually it reports an error saying "cannot open any more databases" and when clicking OK comes back with "An error occurred while sending data to the OLE server (the application used to create the object" and a whole bunch of other messages.

I think it might have something to do with the fact that the form has a number of MS graphs open on it, but I'm not sure. Also, I can't track down a particular line of VBA code which causes this error.

how to update a form after changing the backend database.

View 14 Replies View Related

Modules & VBA :: Using Unbound Form On Front End Of Database To Populate Backend Tables

Apr 12, 2015

Is there any particular reason I would choose to use

DoCmd.RunSQL "insert into...

Versus

CurrentDb.Execute "insert into.... ??

I am using an unbound form on the front end of my database to populate the backend tables (multiple tables). Some forms only have a few fields, while other forms have about 15 fields with 3-4 tables updating.

I am okay using the DoCmn.RunSQL , but I keeping reading on the forum that others chose CurrentDB.Execute and it has me thinking.

My database is stored on a network drive with only on front end.

View 2 Replies View Related

Modules & VBA :: Split Database - UNC Path

Jun 10, 2015

I am using Access 2010 in a split database. The intent is to place the back end and associated linked files on a server and the front end on work stations for each user. The typical user will just be searching for information in the database not altering it. My current code re-links the tables once the path is known i.e. I have to hard code it into the global constant. Is there a way in VBA or utilizing VBA to read/write to an ini file (for example) that the UNC path would automatically be updated to a global constant in my current code that can be updated automatically based on where the back end is installed.

Global Const BE_DATABASE = "192.168.2.3FMCENFMCTABLES.accdb"
Global Const BE_PASSWORD = "***********"
Global Const BE_DATABASEPATH = "192.168.2.3FMC" 'used to open files
Public Sub CreateDatabaseLinks()
On Error Resume Next 'new
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim td As DAO.TableDef

[code]...

View 5 Replies View Related

Modules & VBA :: Split CSV Into Multiple Tables

Jan 15, 2014

I have a CSV file that the system is producing; it is one file with multiple sections in it eg...

Quote:

[Names]
001,"John Doe"
002,"Jane Doe"
003,"An Other"
[Address]
001,"63 Huntly Street","CRANMORE","BA4 7HA"
002,"52 Southern Way","NORTH QUEENSFERRY","KY11 3YZ"
003,"20 Scrimshire Lane","ASKETT","HP27 2AS"
[Orders]
001,"0001A","Screwdiver",1,8.99
001,"0001A","hammer",1,15
001,"0001A","no 3 screws",1,5
001,"0001A","drill",1,49.99
etc etc etc

The file is huge with many sections (eg Names, Address) in it. What I need to do is load it into Access for some analysis.Is there a way I can split the file one loading to the system into multiple tables?

View 14 Replies View Related

Modules & VBA :: How To Use Split Function Query

Feb 18, 2014

I have a query

Code:
SELECT tblZlecenia.ID_Zlecenia, tblZlecenia.TerminOdbioru, tblOffset.ID_RodzajZlecenia, tblZlecenia.NazwaZlecenia, tblZlecenia.Id_K, tblZlecenia.Id_Rodzaj_Pracy
FROM tblZlecenia LEFT JOIN tblOffset ON tblZlecenia.ID_Zlecenia = tblOffset.ID_Zlecenia
where

In where I would like to put code like this:

Code:
Split(tblOffset.DziedziczonePo, ";")(1) & ";" & Split(tblOffset.DziedziczonePo, ";"(2) & ";" = "tak;tak"

Is it possible to create such a query?

View 1 Replies View Related

Modules & VBA :: Split Delimited Field To New Record?

Sep 19, 2013

I need writing a function to split the image names. In essence, I need to create a separate record in a related image table. I have searched over and over again I can't seem to produce the results I need.

I would like to review the records in query so that I can add criteria before appending them.

BV-GB bv-gb.jpg
CSO-C sso-c, cso-c, eso-c.JPG

The number of images for each product varies as well.

View 10 Replies View Related

Modules & VBA :: Users Connected To Split Database

Sep 30, 2013

I have an project with a split database. The data is stored in the database_be.accdb

I want to know which users are connected tot this database.

View 4 Replies View Related

Modules & VBA :: Split Database - Reading Records

Jul 2, 2013

My database was working fine until I split the database and now I'm having issues with this function:

Code:
Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String
Dim rstChild As DAO.Recordset2
Dim fldAttach As DAO.Field2
Dim strFilePath As String
Dim strTempDir As String

[Code] ....

It's getting stuck on the "strFilePath = strTempDir & rstChild.Fields("FileName").Value" line. The debugger says that there is no reord. The strTempDir is correct. I am assuming that the issue has to with linking to the database.

View 6 Replies View Related

Modules & VBA :: Determining Number Of Users Of Split Database

Nov 1, 2013

We have a database that is split. Every user has their own front end (installed via a .bat file so that the users always have a local copy of the most recent version), linked to a shared back end on a server. This seems stable and functional for us so far.

However, because our users are geographically separated, I don't actually know how many folks are using the database. I know the folks who contact me, but everyone in our company could use it if they wanted to.

For requesting funding, guiding future development, etc., I need to get some metrics:

Total number of unique users
Avg uses per day

Total number of simultaneous users (daily, weekly, and monthly maxes and mins)

What is the best way to get this info? Write user info and a timestamp to a table each time a front end is launched? Are there tools for monitoring this sort of information?

View 3 Replies View Related

Modules & VBA :: Split Database - Customize Links And ACCDE

Jul 25, 2013

I have a split Database into Back-End and Front-End. I want to hide the Access Customize Option in my Front-End. I have tried to make the Database ACCDE but the option still appears to Customize the Access Database.

I also want to know why is it that when i make a Database ACCDE - some of the codes are not working properly???

View 9 Replies View Related

Modules & VBA :: Trying To Split A Dashed Number Into Multiple Columns

Jul 22, 2013

I am trying to split a number sequence (Ex. 25372-400-E10-0000-D0021) Where the third number in the sequence can be either 2 or 3 numbers/letters.

The idea is to have a query that will split this number into its individual parts. I have seen several forums about splitting two numbers and such but I can't seem to make that work when I need to create 5 columns from this one field.

View 7 Replies View Related

Modules & VBA :: Remove Specific Filter From Split Form?

Jun 27, 2013

So I have been developing a database tool that needs to have multiple ways of filtering data. I wanted to be able to display the data nicely so I'm using a split form.

As usual, there is an easy to read form on the top half and a datasheet on the bottom half. On one side of the form half I have some comboboxes that are associated with fields in the datasheet. The comboboxes apply multiple filters, narrowing the search results down. It all works fine.

However, I've been trying to figure out a way to remove just one filter at a time. There is a button to the right of each combobox that is intended to clear just one filter.

So far this is what I have. This code clears all filters.

Code:
Function removeFilter(source As String, combo As ComboBox)

'source is the field being filtered and combo is the current
'value of the combobox

With Forms("PART_QUERY")
.Filter = "[" & source & "] = " & Chr(34) & combo & Chr(34)
.FilterOn = False
End With

End Function

I had some success using another method with select statements. Every time I cleared a filter, I would also check every other combobox to see if they were not null, then reapply each one of there filters. That seems way over complicated. I'm sure I'm missing something.

How do I remove one, specific filter from a split form, after I have applied many different filters?

View 4 Replies View Related

Modules & VBA :: Split Data Within A Record Into Multiple Fields?

Sep 3, 2013

I have an access table that looks like the below:

"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"

I've got a module which loads the data into a table, but it can't handle the records that are ~ delimited, so spits them out as a single field.

I know there is some VBA code that can be used to split comma delimited records, and I've seen bits of it floating around online and tried to alter it to work for the ~, but I get the feeling that what I've seen is only a part of the required code, how to put it all together to make something that actually works.

So, what I need to do is;
- Split some records in a table out into multiple fields where there is a ~ present
- Place these newly split fields into a table (I don't mind if it ends up in a new table or not)

View 7 Replies View Related

Modules & VBA :: Find Out Which Records Actually Being Used / Open / Edited In Split DB

Jan 21, 2015

I have a split db in use by about 20 people. Ocasionally a user opens a record that is already opened by some other user and if changes are made then he gets error 7878; "the data has changed error"

Is it possible to find out which records are actually being used/open/edited in a split db? All tables are related to the main key (CallID on table Contacts).

If it was possible to find out, I could have a label show up in the db record search form telling the user not to open that record.

View 6 Replies View Related

Modules & VBA :: Securing Access 2010 Split Database?

Dec 9, 2013

I have a split db with tables in the back end and my forms, reports, code etc. in the front end.

I encrypted the back end with a password. That worked fine.

I deleted and relinked my tables to the encrypted back end. That worked fine as well.

I have a function that will disable the shift key bypass. If I run that in my front end db then I can't save it as an accde because I can't get to the HOME screen. If I save it as an accde first then I can't run my 'disable shift key bypass' function because I can't get to the modules!

How can I secure a split Access 2010 database so that the user cannot execute shift bypass on the front end or make any changes to the code?

View 7 Replies View Related

Modules & VBA :: Multiuser Split DB - Code Causing Occasional 3049?

Dec 2, 2014

I have a multiuser split db with this code on a listbox:

Code:
Private Sub lstsearch_DblClick(Cancel As Integer)

' The following is the variable definition for the error handler
On Error GoTo lstsearch_DblClick_Error
Dim ErrorForm As String
Dim ErrorControl As String

[Code] ....

The error function sends me an e-mail listing the error, user, form and sub. Ocasionally some users get error 3049 when running this sub. It is generally one user out of about 10 or so and the db keeps working fine for everyone else. I have reviewed the back end several times and I cannot find any obvious corruption signs. I have compacted and repaired it several times with very small changes is size.

View 12 Replies View Related

Modules & VBA :: Replace Line Of Code During Split Form Front End Deployment?

Aug 11, 2015

I have a database that I've split and have deployed to 7 persons.

The thing is, I have three (3) forms:

Form 1
Form 2
Form 3

...that require changing a portion of a line in each form's respective VBA codes. I need to change the directories to where an excel workbook is stored after exporting, and another is where same excel is moved to after being uploaded.

At the moment, during deployment I have to manually change the directories.

I was thinking of maybe a pop-up form with four (4) fields that allows the user to select his/her name at the start-up of their frontend app, and paste the directories in the other text boxes provided.

This information will then be used to replace the directories in the VBA.

If I were to make changes and have to redeploy, I would have to do everything manually again.

View 6 Replies View Related

Modules & VBA :: Split Form - Unbound Textbox To Recordset (Vice Versa)

Jul 8, 2015

How to have the function of a splitform to a "mainform and subform"?

I have a main form "frm_main", and a subform "tbl_main" in continuous form which is linked to the table "tbl_main".

I want to be able to click on the record, then the infor will appear in textbox on the main form. I also want to be able to change the value in the textbox and on after update, it will update that record on the subform as well.

So far, in the frm_main Current, I have added the code:

Private Sub Form_Current()
Form_frm_main.lblClient = Me.client_id
End Sub

So now, what code should I add to the After update of the textbox to find the current selected to update back the value of the textbox to the selected record?

View 1 Replies View Related

Modules & VBA :: Record Locking Multi-users Over Network Split Database / Access 2007

Aug 5, 2015

I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.

User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.

What I would like to know is:

1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1

2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1

3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code

Code:
Private Sub Edit_A_Click()
'Get Data to text box control
With Me.R_P_Data_P_Subfrm.Form
Me.txtrID = !rID
Me.txtrID.Tag = !rID
Me.txtrefNo = !refNo
Me.cmbrpc = !rPC

[code]....

View 8 Replies View Related







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