ACCESS Front End MSSQL Backend

[DavisDS]

How Do I do convert my access database so my tables are on a mssql server. I have the server running on my home machine as a server, and am trying to access it from work. I have no clue how to accopmlish this. I have tried DTS from MSSQL tools, I have tried Upgrade manager from Access tools, both of which copy the data into the Server on my local machine, What I don't know is how to connect the Access forms to the MSSQL tables.... ANYTHING PLEASE!!

 

[Remou]

This may help:
How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/EN-US/

 

[DavisDS]

I can access my SQL database by either importing or using VBA when I use (local) as my server from my home machine, but when I try to use an IP :: 192.168.10.5 or 127.0.0.1 to access the same local server by IP reference, the connection fails. Under my MSSQL I have enabled TCP/IP under port 1433. My router does forward that port to my local machine. From my own PC to reference locally (local) works fine. But I meantioned bere The goal is to use the access forms from awork location in which I would HAVE to use ips to reference remotely.....

Still no luck!

 

[CaptainD]

Take a look at this site.

http://www.connectionstrings.com/?carrier=sqlserver

 

[CaptainD]

Another method. I created a module that sets up my connections and I just get my recordset through this.

Function GetADORS(strSql As String) As ADODB.Recordset


Dim objConn As ADODB.Connection


Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=ServerName;Initial catalog=DatabaseName;Integrated Security = SSPI;"

Set GetADORS = New ADODB.Recordset
GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly

End Function

Function ADOExecuteSql(strSql As String)
On Error GoTo ErrHandler
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection


objConn.Open "Provider=sqloledb.1;data source=ServerName;Initial catalog=DatabaseName;Integrated Security=SSPI;"
objConn.Execute (strSql)
objConn.Close
Exit Function
ErrHandler:
    MsgBox "Error executing the SQL statement, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
        objConn.Close
End Function

Then in what ever event I need to retrieve data.

Private Sub SomeEvent()
Dim rs as ADODB.Recordset
Dim strSql as String

strSql = "Select something From Somewhere"

Set rs = GetADORS(strSql)

'Do something with the data
Set rs = Nothing
End Sub

You can do the same thing with the Execute function such as deletes and updates. (Things that do not return data)

 

[DavisDS]

thanks for the help

I keep getting the error message SQL database not found or Access no allowed.

Code:

Private Sub DeleteButton_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=sqloledb;Data Source=75.178.36.198,1433;Network Library=DBMSSOCN;Initial Catalog=myDatabase;User ID=login;Password=pass;"
'cn.Open "Driver={SQL Server};Server=75.178.36.198,1433;Database=database;Uid=login;Pwd=pass;"
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Missions Where ID = " & ID
.Open
End With
MsgBox rs.Fields("Display")
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub

 

[DavisDS]

I've even tried using php to access my mssql with no luck??

I have the service started...

192.168.10.5 is my local IP
75.178.36.198 is my WAN IP

I have Apache 2 on port 80
WinFTP on port 21
MSSQL on port 1433

My router forwards all incoming request to my local IP
If you visit 75.178.36.198 it goes to my index.php file which is specified to access MSSQL with no luck
If you try ftp://75.178.36.198 you will access my ftp server with no flaws. my router is clearly functioning great.

when I use get external data import in Access and reference my sql database as (local) NO ISSUE

when I try the wizard with the specified IP neither WAN no Local IP works... When I try VBA and the above provided code, NO luck

Due to my lack of success in PHP I fear the issue lies in MSSQL but I have read books, search online for hours and am confident my settings are correct, nothing works!

 

[CaptainD]

Your connect string looks good. I assume you've tested the username and password.

Can you ping the IP address?

 

[DavisDS]

Yes I can

as I said the network is not the issue

I can access

http://75.178.36.198

ftp://75.178.36.198

those just port forward to 192.168.10.5:80
192.168.10.5:21
respectively

When I try to access 75.178.36.198:1433
or 192.168.10.5:1433 NO LUCK

thats with both PHP and Access. When I check my Network utility for MSSQL the port is specified as 1433 and its on the same machine, but everything else on the network functions GREAT save the MSSQL

 

[CaptainD]

You did not list what version of SQL server but I found these.

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/778521.aspx

http://support.microsoft.com/kb/884012

 

[DavisDS]

thanx

I have MSSQL 2000
Access 2000

 

[DavisDS]

I will try your second link and upgrade my service pack of server 2000, will post back upon completion

 

[DavisDS]

YEAH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

After updating my MYSQL it now works!!

Aparently there was a distribution flaw with MSSQL 2000 in that you could not access it remotly! As your link explained. the update corrects that issue!


THANKS FOR YOUR HELP!

 

[CaptainD]

No problem, glad to here it worked.

D