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.
View RepliesIn 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
req.Open "GET", "https://api.zdravstvo.gov.mk/rest/prescriptions/" & Me.br_recept, False
req.send
resp.LoadXML req.responseText
I have an application that I have automatically checking to see if tables are valid and connected, etc. If the app finds that the tables aren't connected or aren't working, it automatically tries to re-connect those tables in the background without the user's knowledge. The time I'm having trouble is if the SQL Server doesn't exists (the back-end tables are on a SQL Server). I would like to check to see if the SQL Server exists before I try to re-connect tables so that no SQL Errors pop up but I'm not sure how to figure that out with VBA.
View 3 Replies View RelatedI found and modified this code to allow me to check (fast) if a file exist on a http server or not (without downloading it).
Code:
Public Function CheckNetFile(WebFile As String)
Dim XmlHttpReq As Object
Set XmlHttpReq = CreateObject("Microsoft.XMLHTTP")
XmlHttpReq.Open "HEAD", WebFile, False
[Code] ....
In the above code I managed to declare Webfile as a string so I can call on it later but one thing I still need to tweak is to also get a result out of the test code in a better way than having to publically declare the "CheckNet" variable somewhere else in my app.
First prize would something like this:
If CheckNetFile ("http://www.mysite.co.za/test/test.txt") = 1 then "Yes" else "No"
How do I build the 'booleon check' into the original function?
Hi all,
I have an .adp that works fine on a number of people's laptops.
One of the users however, keeps getting an error message stating that SQL Server does not exist or access has been denied.
This particular user is definitely setup on SQL Server with dbo permissions. She can also ping the server.
I have tried creating a DSN and this doesn't work either.
I am at a loss as to why this error message is occurring given that she can see the Server and has definitely been setup.
Do you have any suggestions?
Thanks,
Kabir
Hi :
I have a question of the connection between SQL Server and MS Access.
I have a project, which is using SQL Server as backend, we use MS Access forms as the frontend.
Firstly, I created the datbase (db1) in the SQL Server, and create a few tables in db1. And then, I create a few forms in the MS Access, I used ODBC connection to link tables from SQL Server to MS Access, it is working fine. Those forms can access the linked tables.
After it is done, I put the MS Access database in the sharing drive, many people can share the files in this sharing drive.
When I go to my associate's computer, and then open the MS Access database in the sharing drive. The forms is working, but, it cannot access to linked tables from SQL Server.
I go back my computer again and then open the MS Access database, it is working fine.
What's the problem with it?
Does anybody need to install SQL Server software and also have db1 database in SQL Server and the path?
I want any user can access the linked tables from SQL Server, but I do NOT want any user can open the SQL Server and open the tables directly. I just want the users can access the linked tables through MS Access Forms only. Because I will make security in the MS Access. So, I don't let the users modify/open the tables in the MS Access, the users must access the tables via the forms only.
Please tell me the steps how to solve it.
Please help.
Thanks.
Hello again,
I have a MS Access project that uses a SQL Server connection to retrieve data. It is set up to authenticate the user every time the database is used, which is what I want it to to.
The problem is the timing of the dialog. In 'design mode', Access tries to show the Database window as soon as the file is loaded, realizes that it has to fetch data from the SQL connection, and prompts the user for the password - this is fine. However, the 'release version' has a different setup. Automatic showing of the database window is disabled, and the user controls the app via a customized menu instead. The result is that Access only prompts the user for his SQL Server credentials when a form is opened - this isn't OK. I would like Access to always ask for a SQL logon when the file is opened, even in 'release' mode.
I'm kinda new to using Access and SQL Server together, but I suppose there is some way of programatically invoking the dialog via a macro, right ? If so, what function should I use, and how do I make a macro that is run immediately after opening the file ?
It would also be nice if there was some way of detecting a successful logon, so that it can reinvoke the dialog indefinitely if logon fails.
Thanks for everything :)
HiI currently have an Access Database in a BE/FE, server/client LAN architecture at a single office location. My client would like to allow one user to 'add new records' to the database on the weekends (i.e. after hours), without needing to come into the office.From my research, it seems I can setup Remote Desktop to connect from the client's home PC directly to the server PC, across the internet, using Windows XP Professional on both computers. Is it that simple, or should I be considering other things?
View 5 Replies View RelatedDear 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
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?
View 3 Replies View RelatedI 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.
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?
View 2 Replies View RelatedI 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...
I am populating Access table using a stored procedure from SQL Server. Here is the code that does the populating.
The problem area is the rst!id (highlighted in red). This is the Identity Column from SQL Server. When Access gets to 32768 it results in a blank in the Access Table. Ive Included the picture.
When I step through the loop and get to that 'id' I can see that the next value is there, by hovering over 'rst!id'. So I know that the value is not NULL but it does not record it in the table.
This continues for the remainder of the load, which is few more thousand rows.
Code:
Do Until rst.EOF
strSQL = "INSERT INTO tblStationPatronageEstimate (pax,transactions,time_band,day_type,entrance, " & _
" from_date,to_date,id,station_entrance_id,number_of_days,average_pax_per_day,
average_tran_per_day,vr_used_name,vr_used,userid, " & _
" time_stamp, completed,comment) " & _
[Code] .....
The results
I have an access front-end package that works just fine but if there is an update, I just want the program to run scripts to update the SQL Server tables that it is connected to. I just don't know how to do this. I know how to do this on SQL Server Management studio and all, I just was hoping I could do it straight from my access front end so that a user could just click a button and update the tables or something.
View 9 Replies View RelatedI have a vbs-script that opens my access database db.mdb and runs the module "Export":
dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("D:Datadb.mdb")
accessApp.Run "Export"
accessApp.Quit
set accessApp = nothing
This works fine as a scheduled task on a Windows 7 computer.
Is there a way to make this work on a Windows Server 2008 R2 ? When I double click the vbs-file, the file opens instead of runs. And when I create a scheduled task, nothings happens.
As part of a larger system I currently have a standalone A2010 program which processes emails that it sees within Outlook. As my client is moving to Exchange Server I was wondering whether there was a better way of doing this by getting rid of Outlook and "talking" directly to Exchange.
View 1 Replies View RelatedThis is a follow-on to my post here: [URL]....
How can I change the code to delete the file if it exists?
I'm trying to store files on a linked SQL Server table, much the way I would using the Attachment data type in Access. (I couldn't use the Attachment data type, though, due to size restrictions - we're storing about a gig a week.)
Almost everyone says to store the attachments on a file system and save the location - I can't do that because of varying permissions. The files HAVE to be stored in the SQL Server table for security reasons.
So how do I do it? I've tried converting the files to binary and updating the recordset that way, but it always comes back as null. I'm not sure what I'm doing wrong.
Code:
Public Function Test(strFile As String) As Boolean
Dim rsAtts As dao.Recordset
Dim ifilenum As Double
Dim btAR() As Byte
Set db = CurrentDb
[Code] ....
I need to map drive on server which is password protected..so i used this:
Code:
persistent = false
set objNetwork = WScript.CreateObject("WScript.Network")
objNetwork.MapNetworkDrive "driveletter:", "serversharename", persistent, "username", "password"
but I get an Error 424 'object required'
in this:
set objNetwork = WScript.CreateObject("WScript.Network")
some other method to map password protected drive
Front end Access 2010, back-end SQL-server 2008 R2.
Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)
or
strSQL = "SELECT myvalue FROM mytable...;"
Set rs = CurrentDb.OpenRecordset(strSQL, 4)
But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset
Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?
I could not find something like DLookup("...) for an SQL-Server or in T-SQL.
I have an Access 2010 application that talks to SQL Server. Part of the application creates tables on the SQl Server and inserts data to it (I know SQL Server has its on data inport/export wizard and bulk insert capabilities but for reasons outside of my control it needs to be done from Access). So I have a connection to the SQl Server and i have VBA that happily executs SQL statements on the SQl Server. The problem is I want to return a recordset from the result of a SQL query issued to the SQL Server. The idea being to check for the presence of tables already created, and if so, append to the already existing table, otherwise if it does not exist, create it and insert data to it.Ive tried the following code but it seems no recordset is being returned:
Set objConnection = New ADODB.Connection
objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes; DATABASE=" & Me.Combo54
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'"
strSQL = "USE " & Me.Combo54 & " " & strSQL
Set Rst = cmd.Execute("USE " & Me.Combo54 & " " & strSQL)