Modules & VBA :: Connection Cannot Be Used - Error On Production But Not Development
Jul 24, 2013
old Access database that's been upgraded through to Access 2000 or 2003 (probably 2000), but not beyond. Was rebuilt in Access 2003 format around 2006/2007 (by someone else who no longer works here). Being used now in Access 2007 and 2010. Uses DSN to connect to SQL Server backend. Last week it was SQL Server 2005, moved the back end on Saturday to SQL Server 2012. Changed the DSN when moving to the new server. But I don't think this has to do with the DSN (it's getting data just fine).
There's a data entry form. User enters a Generator ID, event triggers it to look for the details for the generator and load them into a generator subform. After it loads the information into the subform, and before the user does anything else, it throws "This connection cannot be used to perform this operation. It is either closed or invalid in this context."It triggers post update of the Generator ID:
Code:
Private Sub txtGeneratorID_AfterUpdate()
On Error GoTo Handle_err
Me.txtGeneratorID = UCase(Me.txtGeneratorID)
Call FillHandlerSubform(Me.subGeneratorInfo, Me.txtGeneratorID.Value)
If GetGenStat(Me.txtGeneratorID.Value) = "N" Or GetGenStat(Me.txtGeneratorID.Value) = "OB" Then
MsgBox "Warning Generator Status! " & UCase(Me.txtGeneratorID.Value) & vbCrLf & _
"This Generator has a status of N or OB!", vbCritical, "Bad Generator Status!"
Cancel = True
End If
[code]....
It doesn't throw this error in the development copy of the database, dev copy works just fine. It only throws it from the production version. They are located on the same network, just in different folders. They are pointing to the same database on the same SQL Server using a DSN file located inside the folder where the .mdb file is located (this is a change in the front end, prior to this it was using a DSN on each individual machine, but I changed it to stop that).
I keep the production version of my database on a server, and a copy of it for development work on my desktop. Today I've made several changes to the development version, which didn't work. I then opened the production version held on the server in a separate MS Access window to compare the queries/results to see where the problem was and have found that the changes have automatically updated in the production version. Can anyone explain this? They shouldnt be linked at all. The 2 versions both link to a datawarehouse through an ODBC link, but shouldn't be linking to each other (I'm using Access 2003).
Hi can anyone help me, im struggling to get my database connected to my site, it is connected to dreamweaver through ODBC but will not open, the problem i believe is in the lines of cole in bold. Any help would be much appreciated.
<LI>Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x814 Thread 0xa7c DBC 0x1191024 Jet'. /Project_Wortons/Registration.asp, line 16 Thanks
Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(" coordinatordbTC.mdb")& ";PASSWORD=123456") ================================================== ===================================
I have been trying solve this problem for a few days. however, the best i can come into is the above code, however , the following error still approached me.
================================================== =================================== Microsoft JET Database Engine error '80040e4d'
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
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
I have access application which was running fine recently but for some reason everytime I run it now it gives an error on the adodb connection string saying compile error.
Its doing this on every adodb connection I have in my database.
I have tried registering the dao an ado dlls but still the problem persists.
I think the damage to my database is not recoverable, but i'm still hoping. My computer crashed when I was working on my dtabase and after restart the db is not working anymore :( If I try to do something, anything (!!!) all I got is an error message: "Error accessing file. Network connection may have been lost." But the db is on my computer, I don't use it over the network. What can I do to make it working again? Thanx!
In the code below I want to wait for ten seconds if there is connection to the server, if not to exit the sub with message "No connection to server". How to check it
Dim req As New XMLHTTP60 Dim resp As New DOMDocument60 Dim xmlNodeList As IXMLDOMNodeList Dim myNode As IXMLDOMNode Dim xmlNode As IXMLDOMNode Dim presription As IXMLDOMNode
I am trying to put together a vba adodb connection string to a remote server. It is the first time I am using adodb in this context. I can get msysobj.connect but how do I format that information into a connection object connection string?
How I can check if connection to SQL server is established with VBA before the SQL is executed. btw I use VPN connection to connect to sql server, and some times VPN connection is going down in middle of work and it is quite unconvinced.
I have an excel spreadsheet that uses a data connection to a website to download text. I have the data connection properties to refresh upon opening the file. If i double click the file from windows, it updates. Although using the code below doesn't work. When i've added the code ObjXL.Save before closing it, it usually saves a file with no data and no cells. I'm eventually going to switch visible to false.
Private Sub btnDownLoadSettle_Click() Dim XLapp As New Excel.Application Dim ObjXL As Excel.Workbook Set ObjXL = XLapp.Workbooks.Open("C:...Settlements.xlsx") ObjXL.Application.Visible = True ObjXL.Windows(1).Visible = True Sleep (5000) ObjXL.Close XLapp.Quit End Sub
I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection With connDB .Provider = "Microsoft.ACE.OLEDB.12.0" .Mode = adModeShareExclusive .Open strdbpath 'path to database End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table If connDB.State = adStateOpen Then Set objRS = New ADODB.Recordset objRS.Open strSQL, connDB, adOpenForwardOnly On Error Resume Next objRS.MoveFirst If Err.Number = 0 Then On Error GoTo 0 fieldCnt = objRS.Fields.Count For fieldNum = 0 To fieldCnt - 1 ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name Next fieldNum ws.Cells(2, 1).CopyFromRecordset objRS End If End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"
Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.
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.
I have a picture stored on a network file share that populates each time a particular form is opened. Every so often we lose our connection (which is a different issue altogether). Right now the error I get after much grinding away, is "Bad file name or number"
Is there a quicker way to check for the connection before it spends 45 seconds trying to find the whole path?
Code: Dim vFolderPath As String, dirFile As String, strFile As String vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'")) dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory) strFile = dirFile & "profile_pic.*" 'Debug.Print dirFile On Error Resume Next If Dir(strFile) <> vbNullString Then Me.[ctrl_ImageFrame].Picture = dirFile & "" & Dir(strFile) Else Me!ctrl_ImageFrame.Picture = "X:~stuffprofile_icon.png" End If
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?
I have been developing Access 2K applications. Lately, every time I click a command button on a form or even open the code window for a form, I got this repetitive error message. I even create BLANK Access db and re-import all objects or create brand new form and copy the control designs/code from the error one to the new one. The new db or the new form still have the same ERROR, which is as follow :
"The expression On Click you entered as the event property setting produced the following error: Error accessing file. Network connection may have been lost. *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluation the function, event, or macro."
Please help! Thanks so much for your suggestions/ Have a great weekend,
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...
New to MS-Access and I'm trying to help our HR with creating a production tracking table.
This is just a small simple table with all 12 months of the year and a total production column for summing up everything that has been produced for the year. But being "very" new to access I have zero clue on how to do this. I would appreciate any Access for this dummy help I can get . Thanks in advance.
I have a database which I use for scheduling production. I would like to know if there is a way to "push" the schedule dates. I have multiple products scheduled for production but at times I need to adjust the production dates by pushing all the schedule dates ahead by one day. I don't want to re-enter all the dates to do this adjustments.
My boss threw a curve at me this morning and I need some guidence. I created a commission tracking db for our industry (automotive). Each technician will have a base commission that they'll get regardless of their production. Should they exceed their production by X, we would like to give them a bonus of 2% of all of their work. Should they exceed their production by X+Y, we'd like to increased the bonus to a total of 4% for their work during this pay period.
I need help with the table stucture, to start. My db currently contains a variety of tables including tblTech. tblTech contains information specific to each tech including their annual PERSONAL income goal (not to be confused with Bonus scale). Depending on a number of factors (ie. how long they've worked here, how much other responsibility, etc.), I'd like to assign a bonus tier to each technicial. ie. Technicial A = Joe. Joe is promoted to manager and therefore needs a greater bonus for the same amount of production. I'd like to assign him to Tier 2 where he makes more then a Tier 1 one employee.
I'm having a hard time getting focused. All suggestions are appreciated.
I currently have a continuous form in my database that has 24 records in it. every record is blank except for a time (I will post a screenshot)
Our employees are instructed to enter various information every hour in this form. coding a button that, when clicked, will create 24 new records in the table that this form is based on, insert times in those 24 records, and then display those 24 records in the form. It would have the illusion that the end user it "resetting" the form at the end of his shift and preparing a clean form for the next person.
In the spreadsheet that we currently use, there is a button that, when pressed, saves the workbook with the date in its file name and then clears all of the end-user's data so that the next shift has a clean spreadsheet to use. I would like to mimic this button as closely as possible in MS Access
I understand that this database design is not exactly ideal in the world of database engineering; however, I was told to make this a perfectly seamless transition from the excel spreadsheet we currently use (there is a folder with 900+ workbooks from over the years. This obviously makes the information useless - we need all of the information in one file - this database)
I want to release an Access program to the production environment for users. I remember there being a file extention that can be used so the users cannot access the DB or Code.
