Split Database Persistent Connection
Jul 6, 2015
Using Access 2013.
We have split a database and have about 6 users connected to a gigabit switch. There is a noticeable delay of about 5 to 15 seconds when we search by an account name. We are working with a split form, where it has the database records listed at the bottom half of the screen.
Is there a way to improve the performance? I tried a persistent connection, but it didn't seem to work and would occasionally get an error message.
View Replies
ADVERTISEMENT
Mar 16, 2015
I read a lot about Access database bloat when using temporary tables that reside in the front end, which was where I created and used temporary tables before reading about bloat.I therefore wrote a routine on startup that creates a temporary database and then copies the structure of the temporary tables into that temporary database using the DoCmd.TransferDatabase command. It kills the DB on exiting the application.I also read about keeping a persistent connection (handle) open to a linked DB as long as the main application is active, and this for efficiency reasons. I got this to work as well.
Being a self-taught Access programmer I have two questions:
1.Is the use of docmd.Transferdatabase a good way to work and does it reduce bloat by transferring the structure of the temp files to the temporary database?
2.If I want to work with the tables in my temporary database I use the following code for example:
Dim dbTemp As Database temporary database
Set dbTemp = DBEngine.Workspaces(0).OpenDatabase(strDBTemp)
Set rstRpt = dbTemp.OpenRecordset("tblRptPU001", dbOpenDynaset) ..
My question is this: if, at the end of the procedure, I write
dbTemp.close
set dbTemp = Nothing
does this leave the persistent connection open? The reason for the question is that I intend to split the database and soon go multi user, which is when efficiency is even more important than when working on a temporary database.
View 4 Replies
View Related
Apr 15, 2006
My current work suffered some kind of internal error and two imported objects, a form file and a module, became logical orphans. No code is visible, and I get a message whenever transiting to VBA: "Error accessing file. Network connection may have been lost." Needless to say (but I will anyway) I am not on a network.
I was able to purge the objects from the 'database' dialog but NOT from the project explorer in Basic. Will anything short of dynamite do the trick?
Alexander
Access 2000
This works: open a blank database then get external data from the broken .MDB. The orphans get left behind. There's got to be a better way?!
View 1 Replies
View Related
Sep 30, 2005
I have a combo box that is populated by a query. The problem is that the query pulls data from a different database than the rest of the form and it prompts the user for a password to access this database.
Is there any way of coding the password into the form so that the connection to the database is opened automatically without prompting the user?
Thanks.
View 4 Replies
View Related
Dec 25, 2004
I'm trying to use a banner ads .asp program called Admentor.
My problem is that this uses DNS less connection and I can't get this working on both the pages showing the ads and the admin panel.
I know it's the path to the database that causes this.
If I change this to suite the webpages showing ads, it work's, and if I modify it to suite the admin panel it work's.
But not both at the same time.
Should'nt this path be relative to the sites root ?
g_Admentor_strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="& Server.MapPath("../ads/admentor/ad97.mdb")
when using this string, the webpages showing banners is working, but when clicking on these, the site calles a redirect .asp page that also uses this database connection. Then it fails, since this page is located in another folder in the site and therefore also gets another path to the database.
How can I get this to be a relative path working on all pages not been worried about folders, subfolders and correct paths to the base ???
Best regards
Ketil Leinum
Norway
View 1 Replies
View Related
May 9, 2005
hi, below are the codings i input.
I just cannot get the connection to open. error is at "objConn.open"
I have also given the user full access right. folder attribute however, always reverts back to "read-only" whenever i try to turn "read-only" off.
Please help!
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source="& server.MapPath("db.mdb")
objConn.Open
sql = "Select * From tblReceipt"
objRs.CursorLocation = 3
objRs.CursorType = 3
objRs.ActiveConnection = objConn
objRs.source = sql
objRs.open
View 1 Replies
View Related
May 1, 2007
Hi, I went to school over 4 years ago learning ASP but I forgot almost everything.
I am able to setup a connection to a an Ms Access file and get information using Windowx XP's IIS.
I need to change the code where the location of the Access file is on my computer to my webhost.
In other words, I have uploaded all the pages and the Access db file but now the pages can't find the Access db because the file was original on my computer in a certain folder. Now that it is uploaded, I need to change the location where it searches for the db from my computer to my webhost.
I have not dealt with ASP for almost 5 years ago.
Please give advice, or websites or 3rd party programs that can do this for me.
I am trying to build a webiste where you enter the username and login then be able to retrieve information or update, or add new or delete from the Access db.
This should be a simple problem for most you professionals out there.
View 2 Replies
View Related
Jul 26, 2005
Hi,
I apologize first of all if this question is bounced up and down through out the forum all the time, and if there's already a topic on this, then it'd be perfect for me. But im not having any luck.
I do not know how to upload my database in a way that it will work when i try to use a page through microsoft frontpage to display results from it.
What are the accurate steps into creating a database connection? Thanks a bunch
View 3 Replies
View Related
Mar 9, 2007
Really don't know the answer to this question and I thought I'd come to the experts. I have created an Access database that I use to push & pull data from an SQL database through linked tables. I created an ODBC connection on my PC and set it so the links keep the username and password for the ODBC connection. All that works great...Now my question - Is it possible to somehow "store" the ODBC connection within the Access database so that I don't have to go to every PC that is going to be running this database and create an ODBC connection? The Access database and the SQL database both exist on our network and all those that will be using it have access to both. If someone has the answer I would really appreciate it.
Thanks
View 8 Replies
View Related
Nov 24, 2004
Hey,
Im doing a login page for my website, and i am having trouble connecting to the DB:
'Database connection info and driver
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=letmein; DBQ=" & Server.MapPath(strAccessDB)
where strAccessDB = "cs334-45/Kosy/Kosy.mdb"
cs334-45 is my computer name in the virtual directory.
Im getting this error:
Server.MapPath(), ASP 0172 (0x80004005)
The Path parameter for the MapPath method must be a virtual path. A physical path was used.
/Kosy/check_user.asp, line 21
I dont understand. I thought that the virtual directory was mapped to the physical directory
anyway? So why do I have to enter a virtual path?
View 1 Replies
View Related
Mar 24, 2014
There are hyperlinks throughout the database as well as a few lines of code that reference network locations that could change 1-2 years down the line. What I need to do is build a "housekeeping" form that is password protected where users can edit those hyperlinks via text-boxes. My problem is I'm not sure where to store the the data from the text-boxes to make it persistent. Making a table for the hyperlinks seems to be the wrong approach but I could be wrong.
Example:
Form1-Button1="String1"
String1="HousekeepingForm-TextBox1"
HousekeepingForm-TextBox1="X:Stuff"
View 8 Replies
View Related
Apr 9, 2006
hi guys,
this is my first thread on this forum...,, i am new out here..
i am not able to establish a connection with a MS Access database. I am using DSN Connection for it..
The error that i am getting is...//
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
/airtickets/scripts/dbconn.asp, line 4
please help...??
View 2 Replies
View Related
Nov 18, 2012
I have a back end 2002 DB. I would like to disallow access to this through odbc connection. Is there some way this can be achieved? Thie directory where it is located is unfortnately cannot be restricted as the front end mde also resides there.
View 1 Replies
View Related
Jun 26, 2015
I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?
View 2 Replies
View Related
Jan 10, 2013
we have Oracle 11g as main database and for viewing purpose, we have created an mdb (Access 2007) using Link tables via ODBC connection (system DSN).While querying, count queries are working fine with accurate record counts but, all select queries are not working fine. It filters and displays with wrong records. There is no issue in query building.how to get correct records through odbc connection to Oracle database.
View 3 Replies
View Related
Sep 26, 2005
Dear Guys,
How to Establish the connection between Ms-Access2000 & Oracle using DAO Connection through Code?.
I created one table in Oracle and I created User-DSN in ODBC.
I linked that oracle table into Ms-Access MDB using Link table option in ms-Access.
Now, Whenever I open that mdb, we need to establish the ODBC connectivity automatically through code (I mean, we need to refresh automatically).
I need to hardcode all the information in the Ms-Access code.
My User DSN Name: abcd
Oracle Database Name: abcd
Schema Name(User Name): abcd
password: abcd
View 1 Replies
View Related
Sep 4, 2014
I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...
I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.
Only way I can relink is to change ip adress in conn.string
E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.
It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...
View 12 Replies
View Related
Jan 12, 2006
:confused: I have read a quite a few threads on spiting the database. My database is 50 Meg and running very slow. I have compacted and repair, still the same. This just happen all of sudden. Could that be possible?. What should I look out before I try to spit it?. It’s given that I will make a backup. I am running Access 2003. I have over 150 users.
View 5 Replies
View Related
Aug 12, 2006
I am almost at the finishing line with a great deal of help from here.
I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago.
I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships.
The final step is to make it an MDE file so that it can go on the local network at work (not on the internet).
My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
Any advice, as always, is most welcome.
View 4 Replies
View Related
Nov 21, 2006
I have a database that resides on a file server share. I have split the database to create a backend. I then posted a shortcut of the front end to the desktops of two users who have access to the share. I then tried to access the front end from the two machines simultaneously. It opened on the first PC but not the second? When I viewed the server share it had a 'padlock' icon?
Where am I going wrong? This is the first time I have attempted this scenario!!
Thanks in advance,
Phil
View 4 Replies
View Related
Dec 21, 2006
Hello,
Should splitting only be done when all tables,queries,forms,reports, etc. are done. Or can it be done anytime. I am almost to that stage but didnt want to jump the gun and run into problems later on.
Thanks.
I will have about 20 users sharing this.
View 6 Replies
View Related
Dec 27, 2006
I was having a discussion with Rickster57 concerning my newly developed database, and he recommended that I split it so that it has a front end and a back end. Rick listed a number of very good reasons for doing this (so I will definitely do it). But I wanted to pose the question so I could get some of the reasons the more seasoned Access programmers have for creating a front and back end to their programs.
View 9 Replies
View Related
Jun 29, 2007
I recently split my database and also used Bob Larson's Autoupdating tool. Everything appeared to be working fine until a few users were unable to access the db. I later found out that because they are at a different site, they don't map to the server that houses the database BE. However, even if they manually map to the correct server, they are unable to bring up the db Form. They get an error stating the BE "...is not a valid path". My questions are:
1) If I were to copy all the files (the Master FE, BE, and MDE) to a public folder that ALL users have access to, will everyone be able to bring up the database Form?
2) How would this affect what was already set up when I ran Bob Larson's utility (or even the splitting and MDE creation)?
3) Is there a way to "Undo" what was previously "Done" to this database (ie: Splitting, Larson Utility, MDE creation) in order to correct the issue, or do I need to start from scratch and split the database while it's on a Public drive.
I hope this makes sense. Thanks in advance for your help.
Michael
View 10 Replies
View Related
Jul 2, 2007
Hi!
I just want to know that " How to split the Access database in Front End and Back End application"? I am creating a database that i want to store on server and simultaneously want to give access to 20 terminals (cleints) to access the database.
Secondly, I have to provide a field in a form "Document number". However as per the requirement user can enter "PIR No", "Serial No" or at times both as document number. Both the things "PIR No" and "Serial No" have different format types like "PIR No 001" and "Srl No001".
On basis of document number later i have to give the option to search the particular document. Please guide me how to provide the option to enter the field values.
Appreciate any help on above.
Regards,
Nick
View 3 Replies
View Related
Aug 12, 2006
I am almost at the finishing line with a great deal of help from here.
I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago.
I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships.
The final step is to make it an MDE file so that it can go on the local network at work (not on the internet).
My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
Any advice, as always, is most welcome.
View 12 Replies
View Related
Sep 11, 2005
I am combining 12 Databases. I have split all 12 between the server and the desktop. My question is “Should I combing all of the data table on the server into one database or should I leave all of the individual application data tables in separate Databases on the server”. Additionally, is there a limit to the number of tables an Access DB can handle? My inclination is to keep the functionality separated but the problem I have is that some of the functionality within the applications overlap. Recommendations!
View 2 Replies
View Related
