Splited Database not working correctly

[jdcallejas]

Hello Everyone,

Hope all is good with you. I have not been here in a little while and I need your help. I am building a database and so far it works great. The problem I am having is that it only works well when the database it’s a full database, meaning tables are within the program, not split.

What I need to do is split the database, I am able to do that in access, I then put the BE on a server we have in the office. The problem begins when I link the FE to the BE, Some forms don’t show up the details or fields, some update queries don’t work and much more.

If the FE is in the same CPU and the tables are linked I have no issues, the problem is when I link FE to the BE from another CPU on the network.

Has anyone encounter this issue before? What am I doing wrong? I have done this before back in 2003 with a large DB and didn’t have any issues.
If this helps I will attach the DB so you can try to duplicate the problem.. Just one thing, FE is in Spanish.

Thanks

 

[spikepl]

If you have OpenRecordset anywhere do not use dbTable but dbOpenDynaset

Other than that (concerning "it doesn't work") - read my signature.

 

[jdcallejas]

Thank you for your responde! And I like ypur signiture!! Its postive!

 

[The_Doc_Man]

One thing that most new users of Access don't appreciate is that a query and a table are almost perfect equivalent - but if you make a single-table SELECT query and use it in place of the actual table, Access can find the back-end better. Not only that, but some of the "behind-the-scenes" stuff underneath forms and reports knows that they should automagically open the recordset differently for queries vs direct tables. It's subtle, but I have never had trouble with forms and reports when I take the approach of building the queries as the "real" intermediaries.

My answer should in no way be taken as disagreeing with Spike - rather, it describes a different experience among the many that Access can give you.

 

[CJ_London]

I agree with both spike and doc -but also, you need to consider how your forms and reports work.

Every bit of data has to come across the network to your PC. So the more your bring across, the longer it takes - plus if network performance is not great, nor will your db.

Does your form/report recordsource (or combo or listbox rowsources) bring through entire tables - or are they filtered to only bring through what is required - in terms of both width (number of columns) and depth (number of rows).

A classic example is a form with just a table as recordsource and you use the navigators at the bottom to search for records, but also have an unbound combobox to find a record. In this situation you have loaded an entire table, but you are only looking at a record at a time.

So better for your form to have a recordsource like

SELECT * FROM myTable WHERE ID=0

which will leave the fields blank, then to have in your combo afterupdate event

me.recordsource="SELECT * FROM myTable WHERE ID=" & me.combo

which will then display the selected record.

Are your tables properly indexed - all fields which are regularly searched/grouped on should be indexed.

Do you use 'Like '*something*' type criteria in your queries - anything which has a * at the beginning results in a sequential search through the data, rather than an indexed search so will be significantly slower.

Are you using lookups in your table design? among other things, these have an enormous impact on performance across the network.

Also, look at maintaining an open connection between FE and BE. If you don't, every time you open a form a connection has to be made which affects performance. To maintain an open connection, depends on how your db works - a common way is the initial form to open that makes a call to the BE (typically a login form) is not closed, but instead hidden, thereby maintaining an open connection. You can check by looking for the .laccdb file in the same directory as the BE - if it is there, there is a connection.

But getting back to data volumes - the transmission speed between a local drive and CPU is an order of magnitude faster than across a network - Lets say 100 times. So if you are bringing through 100 records, when you only need 1 - well do the math

 

[jdcallejas]

Hello again and thanks for the input,

I will explain in details what I have done and what is happening.

I have created a database for our company; I have some experience with access. In the year 2000 I created a database in access when I worked as an intern in Alachua Count Florida. Thanks to this forum I created a database that won the National Initiative Government Program. It’s been a while since I used access but it’s a great database program…

I started this database about a month ago; I was always working on it on the same drive. All my select queries, update queries, reports work great on the same drive. Now it was time to implement the database and I needed to split so I can put it on a server we have.

The action of the split was successful and I placed the BE on the server.

As Spikepl listed on his signature;

1. What I want it to do?
I want to be able to put on each department only the forms they need to use, this was successful by just giving them the FE for each department. I was able to link the tables to each department successfully.

2. What is happening?
a. When I open a some forms on a remote PC, this includes subforms, I get a blank space as seen on the image. I do not see any of the fields.

b. When I click on add new record on the remote PC, I get an error saying “You cant go to specific record” as shown on image.

c. I also notice that if I already had records on table it does show me the fields and am able to navigate the records.

3. Network Issue?
a. I connected the server directly to another PC using a crossover cable; they both have gigabyte Ethernet cards. Speed it’s incredible. I continue to get the same issues explain above.

4. The system when I have the same form open on remote and local PC they both come out blank.

Spikepl, I am not sure where to check what you wrote to me “If you have OpenRecordset anywhere do not use dbTable but dbOpenDynaset” maybe this could be the problem, I did some google search, I also checked on all my VBA code for this and didn’t find it.

I think it must be something simple, I just don’t know where it is..

 

[jdcallejas]

Hello all, hi spikepl,

I got my database working, I am not sure what I did, the only thing I can think of is that when I linked the tables this time, I did not select "Always Prompt for new location" and just let the system ask me where...

I am having one issue, I have an update query, this query updates the a value from a table that is directly on the FE to a table in the BE.

When I buy products I input this into an invoice, this invoice has its subform details. On each line of the subform I input the productID, the amount that was bought and the unit price.

When I click on close form, I have the following code:

Private Sub Command17_Click()
DoCmd.Close
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryCreate_Table_LastProdCost", acViewNormal
DoCmd.OpenQuery "qryUpdate_Table_Prod_Cost", acViewNormal
DoCmd.Close
DoCmd.OpenForm "frmMenuInventario", acNormal
End Sub

When I do this from the FE that is in the same directory as the BE it works fine, but from any other PC I get an error msg that the field is not updatable.

So I duplicated the query on that particular FE but I still got the same error msg.

Any Suggestion?

Thanks