Hi I have been trawling through forum after forum trying to locate a solution to my database connection problem and I raised this issue with Winhost prior to subscribing and am assured that a solution is possible. However I am struggling to secure the connection - a combination of jargon terms and my limited detailed knowledge of 'database connecting'. Background I have developed a Windows-Based Form Application in Visual Studio. This application manages project information (the database) and facilitates via a graphical user interface, access to standard company templates (word/excel etc.). This is working satisfactorily within our company network, i.e. a central server, hard-wired to each workstation. This system also works when colleagues use VPN to 'dial' into the office since the UNC pathname remains the same. VPN is however very slow and without VPN our other offices cannot access the database stored in our office HQ. Future Development In order to take this application to the "Next Level" and incorporate access from all other offices and facilitate a faster database connection I want to re-locate the Microsoft Access Database to a secure off-site location - on a web-server - hence Winhost. This will/should allow users speedy access to the database from anywhere, regardless of whether they are in the main company HQ. The Problem To successfully connect to the database on the local server (existing situation) I am using the following code and connection string... Code: Public Sub connect2Database() 'DEFINE A SQL STATEMENT strSql = "SELECT * FROM table1" 'CENTRAL CONTROL DATABASE - DEFINE CONNECTION STRING [B] connString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=//server/shared/database/central_control.mdb"[/B] 'CREATE AND ESTABLISH A NEW CONNECTION TO THE DESIRED DATABASE conn = New ADODB.Connection 'OPEN CONNECTION TO DATABASE With conn .CursorLocation = ADODB.CursorLocationEnum.adUseClient .ConnectionString = connString .Open() End With 'CREATE AND ESTABLISH A NEW RECORD SET TO CONTAIN THE DETAIL FROM THE DATABASE rs = New ADODB.Recordset 'OPEN RECORD SET With rs .CursorType = ADODB.CursorTypeEnum.adOpenDynamic .LockType = ADODB.LockTypeEnum.adLockOptimistic .ActiveConnection = conn .Open(strSQL) End With 'CHECK THAT END OF RECORD SET IS NOT FOUND If Not rs.EOF Then 'MOVE TO THE FIRST RECORD rs.MoveFirst() 'WHILST NOT AT THE END OF THE RECORD SET Do While Not rs.EOF 'PERFORM ACTION WITH DATA FOUND 'MOVE TO NEXT RECORD rs.MoveNext() Loop End If End Sub Now, I don't claim to understand the pro's and con's of the various connection types and terms ADO OLEDB ODBC etc, all I know is that the connection I am using 'works' and is currently integrated into the windows application that I have developed - so I would be reluctant to change the whole application unless absolutely necessary. I understand that, theoretically, it is possible to connect in the same way using the following connection string... Code: connString = "Provider=MS Remote;" & _ "Remote Server=[B][COLOR="Red"]ServerDetail[/COLOR][/B];" & _ "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=[B][COLOR="red"]PhysicalPathToDatabase[/COLOR][/B]" Also applying the following changes to the previous code (highlighted red)... Code: Public Sub connect2Database() 'DEFINE A SQL STATEMENT strSql = "SELECT * FROM table1" 'CENTRAL CONTROL DATABASE - DEFINE CONNECTION STRING [B][COLOR="red"] connString = "Provider=MS Remote;" & _ "Remote Server=ServerDetail;" & _ "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=PhysicalPathToDatabase"[/COLOR][/B] 'CREATE AND ESTABLISH A NEW CONNECTION TO THE DESIRED DATABASE conn = New ADODB.Connection 'OPEN CONNECTION TO DATABASE With conn .CursorLocation = ADODB.CursorLocationEnum.adUseClient .ConnectionString = connString [B][COLOR="red"].Open(, UserID:="admin", Password:="")[/COLOR][/B] End With 'CREATE AND ESTABLISH A NEW RECORD SET TO CONTAIN THE DETAIL FROM THE DATABASE rs = New ADODB.Recordset 'OPEN RECORD SET With rs .CursorType = ADODB.CursorTypeEnum.adOpenDynamic .LockType = ADODB.LockTypeEnum.adLockOptimistic .ActiveConnection = conn .Open(strSQL) End With 'CHECK THAT END OF RECORD SET IS NOT FOUND If Not rs.EOF Then 'MOVE TO THE FIRST RECORD rs.MoveFirst() 'WHILST NOT AT THE END OF THE RECORD SET Do While Not rs.EOF 'PERFORM ACTION WITH DATA FOUND 'MOVE TO NEXT RECORD rs.MoveNext() Loop End If End Sub I am satisfied that Winhost will not allow public access to the database when hosted on the site, i.e. navigating to the URL and I am also aware of my site 'variables' detailed within the Winhost control panel - ftp address, IP address etc. I have also successfully established the IIS connection to my site. But this is where I begin to get lost... HELP!! I cannot for the life of me work out how the set up and establish a successful database connection - my initial impression was that it would be a straight forward swap of connection strings (and this may still be the case) but I am now getting lost in the other jargon - virtual directories/application settings/DNS/DSN. Please can someone offer me a step by step guide to setting up and establishing a connection to my database from a Remote VB Windows-Form Application. I do not intend to execute the connection script from the local webserver as all of the other ASP-style solutions seem to suggest. Fingers crossed and many thanks in advance.
You cannot use a UNC path name. You can only use the direct server path name. Which means the Access databsae basically has to be uploaded on the same machine as your web application resides at. Remember that Access was never meant to be a Database that can be connected to over the Internet. It is strictly a desktop database. This does not mean you cannot use Access as you back end database for your web pages, but to do this then you also need to upload the Access database to our web server. The path to the Access database will have this format... E:\Web\[root folder]\ ..and not http://my_own_network.com ..or //servername/sharedfolder Only a true database server such as SQL can do this. But this will require that you migrate your data out of your Access database and into our SQL Server.
Hi Ray, Thank you for your prompt reply. I had hoped that my original post would clearly outline the problem I am having. I have in the past managed an ASP based website which did indeed use the "E:/iiswebroot/domain/....etc" path name for the database. More over, since the webpage and the database were on the same server I appreciate that the link to the file does not have to dial into the server to locate the source. I also appreciate the other comments you make, however the trouble I have is...(and forgive me if I have the wrong terminology) The application that I am developing is not based around a website and is not hosted on your servers. I intended that this windows application, running locally on the client computer (my computer for arguements sake) queries the database (hosted on Winhost) to populate locally defined variables (within my windows application) that then feed back into associated windows forms and not an ASP webpage. I just want to remotely host an access database so that people can 'dial in' to query and retrieve information. Anecdotally similar to the following... Similarly (perhaps) to say the Google/Vista Tool Bar where, when loaded the news feed connects to a data source and recalls the top ten news articles. Whilst I appreciate that this may use a different technology, this is the type of 'connection' I am seeking. Kind regards Dave
No this is not going to work. If you have a program running on your computer (argument sake) and you want it to connect to a Access database that is uploaded to our server, it will not work. This is not our doing. This is by design by Microsoft. Access was never intended to work like this.
Ray In some ways, that is an answer I was looking for. Are you aware of a way in which I can achieve what I am trying to do? You help is appreciated. Dave
The only way is upsizing our migrating to a SQL Server. Of course this will mean that some of your coding will change, specifically your connection string.
