Modules & VBA :: Verify A Network Connection / Bad File Name Or Number
Mar 22, 2014
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 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!
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 a button that adds pictures/files to a network location. So people from all over the company can do this and all pictures/files are stored in one location.
I also have a delete button, but a waning pops up and the file remains on the network.
PHP Code:
Run-time error '424': object required
Code: My.Computer.FileSystem.DeleteFile (Path)
where Path is a string, files location on network.
I have a small access database that my users keep open all day long. Access 2000.
I have one primary version of the DB on the network. No one uses it. I have another file on the network that has linked tables to the primary db. I copy a version of this DB to each workstation.
So each user has a full blown DB on their desktop with linked tables back to the main DB. Is this a good set up or are there any issues doing this? What is the best set up for multiple users across the network? Thanks.
I'm currently working with Access 2007 building a way to create PODs to clients. I have most everything laid out, one problem I can't seem to get around is naming the file the SOW #. Below is the code:
Function Run_all_PODs_01() Dim myPath As String Dim name As Object
DoCmd.OpenQuery "Q5 SOW bill requested data points all", acViewNormal, acEdit DoCmd.OpenQuery "Q5 SOW bill requested All 01", acViewNormal, acEdit
[Code] ...
I can't seem to get the name to equal the SOW number.
The table that I'm trying to reference is "SOW bill requested data points" it is a basic table one row with a column header as SOW #. I'm not sure how to tell it name the file that data point in the column.
Hi there, we currently have a database that users write to through an excel form. Each time a user sumbits a record to the database we open a connection up with the below code, insert the record and then close it. This creates a ldb file for 1-3 seconds depending on how long it takes.
' OPEN DATABASE CONNECTION Set dbConn = New ADODB.Connection dbConn.CursorLocation = adUseClient dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" dbConn.Open sConn
' INSERT RECORD THROUGH EXECUTE COMMAND dbConn.Execute("INSERT INTO.... VALUES....")
' CLOSE CONNECTION dbConn.Close
Is it possible to open the connection up without an ldb file being created so more than 1 user can insert a record into the database at the same time?
I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26 database number = 41397.4763888889 Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.
Code: Private Sub Command288_Click() Dim s As String Dim t As Integer Dim ws As Worksheet s = LaunchCD(Me) MsgBox (s)
[code]....
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.
Code: Private Sub LoadData_Click() Dim w As String w = Forms![frmList1]![Combo0] w = "[" & w & "]" Dim dtA As String
Hi, I have created a File DSN using my ODBC under Admin Tools and placed it on the network and have linked my Access 2003 tables to point at this.
I have asked a non-domain user to log into this database and open a table but when they do, it defaults to a Domain account until you untick trusted Source. You then have to enter the username and password for the ODBC connection. I thought the whole point of a File DSN was to eradicate the process of logging in. Can anyone Help?? Thanks
This is what the ODBC looks like through text editing software.:eek:
[ODBC] DRIVER=SQL Server UID=MyUserAccountName LANGUAGE=us_english DATABASE=MyDatabaseName WSID=MyWorkstationName APP=Microsoft Data Access Components SERVER=MyServerName Description=MyDescription
I have 2 files with linking tables. Both are posted on the same network drive for the office to access. However some users have their drive link to drive E: and some to G:, etc. How do I get the file to automatically find what drive the user has and update the correct link in Linked Table Manager?
I am running a small access database over a wireless network which all worked fine (still does on 1 wired/3 wireless) with not too many problems total of 6 machines (5 wireless/1 wired). I have just replaced two machine as they were getting a bit old 3years. But these two new machines do not seem to have full rights to the access database, in that I can open the database and view records but when you add data you get a Disk Error message. I can open and share word documents etc but not the database if I reverse this set-up I get the same problem the wired computer can open and save word documents but again not the database - what am I missing!!!
SET-UP = All machines have Windows XP Pro and Office 2003 with all windows/office updates done, all connected to the same workgroup etc. Two new machines are Dell with Dell Wireless adapter. No server just workgroup and database in a shared folder on one computer which is hard wired to the Netgear wireless router. I have not run any network wizards and I dont think I need to, just sharing the folder should be enough? Windows is managing the dell wireless adapters, file and print sharing is enabled, other computers can print to this computer, but I just cant get the database to share correctly I have also checked References and all looks fine.
I have created the time booking application in access. (Application will alow autherised user to enter daily work done) My Idea was to put this file on the server and ask each employee to log in and enter information. But whenever I try to run application from other than my pc(On which file is created) it flashes error and can't run some of the form. It works absolutely fine when run from my pc. Why this hapens? What is the solution for this? Can access application is sharebale over network?
I have created multi user access application. I have kept it on server and all users access that application from there desktop pc. Can I set time out for application? Means, Session of each user must be note more than 10 minutes. I want to add this functionality for avoiding the application blocking.
Here are some background . My company got a access file in a network drive.The data entry simply open the file in the network drive.But once the data has increased (~400mb) sudddendly,all process slow down. Like search , using report etc.Is there any method to imporive the situation?Some guys suggest me to copy the data file to the desktop and sync them.Can it it work?IF works,how to do that?
Does a (the) .ldb file have to be closed for another user on a network (separate FE linked to network drive BE) to read/write info to a table? Or even select info from a table?
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.
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 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.
First, I'm developing both the Front End and Back End in Access 2007, and I intend to distribute the application using the Access Run Time. The powers that be will not let me use SQL Server.
The database is required to house attachments. Initially, I had created a separate back end database to store the attachments, but the file would quickly grow beyond the 2GB limit - roughly within 3 weeks. Instead, I decided to save the "attachments" in folders (named after the parent records PK ID) on the network. Using VBA, I would populate a list, based on the parent record, from which the user could select the file, do what needs to be done, then save it.
Unforunately, we're running into data security issues - all of the attachments will have private personal information - SSNs, Loan Numbers, Account Numbers etc., so they need to be stored in a location that's not universally accessible. All of the users have different permissions based on their department, acting as a very basic security level (i.e., Department 1 users will not be able to view any parent records associted with departments 2 or 3).
So I need a way for my back end to take a file located in a separate directory not normally available to the Front End User, then move that file (or save a copy that will be overwritten when re-attached to the record) to a location that IS accessible to my Front End User.
Can I create/use modules stored on the back end?
My first thought is to let the back end store the record in a temporary attachment table that is deleted when the user is done with it, but I've noticed that Access has trouble reclaiming the space when the attachment is ultimately deleted once the user is done using it, and I'll run into my 2GB limit again.
Alternatively, I could create a new .accdb file each time the record's attachments need to be accessed, and then delete that database once it's done and the attachments are returned to the secure location, solving my limit problems.
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?
