DSNless ODBC Connection to SQL Server from Acess for Scalable Updates

What port is the SQL Server listening on? If you know a specific listening port you can add an ADDRESS=SQLSERVERIPADDRESS:Port; after the PWD ;

I'm not sure, I've purchased shared webspace, so when I'm in Management Studio and I connect to the actual 'server' I see many, many databases but, I can of course only access and modify my personal database.
How would I check what port it's operating through? And, is there a way to check while in access whether or not I'm actually connected to the sql server?

The host should be able to provide you with information pertaining to the DSN and which ports that SQL Server is listening on. I'll bet that they have ports 1433 and 1434 firewall blocked due the the old Slammer worm problem.

Well, I just got off the phone with my webhost and they said that they have the sql server listening on both 1433 and 1434. So, it shouldn't be a port problem since those are the default ports.

On the :
Public Sub UseConnectionODBC()
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection

cnn.Open _
 "DRIVER=SQL Server;" & _
 "Server=xxxserversaddressxxx;" & _
 "Database=DBname;UID=xxxx;PWD=xxxx"
 
Debug.Print cnn.ConnectionString

End Sub

Is there away after running the function to have a message box saying what the current connection is? I guess the key element that I'm looking for initially is whether or not the connection is being made successfully and testing for that. B/c if the connection isn't successful obviously nothing else will be.

Alright I was looking on the internet as to other ways of connecting and I ran across this piece of code

Public Sub CreateTable()

    Dim strTableName As String, strConnect As String
   
    'This connection string creates a DSN-less connection to the server (called PMSR in this instance)
    strConnect = "ODBC;Driver={SQL Server};SERVER=****;DATABASE=****;UID=****;PWD=****;"
    'What I want the table to be called
    strTableName = "tblCurrentProducts"
   
    'Check to see if table exists.  If it does, delete it.
    If TableExists(CurrentDb, strTableName) Then DoCmd.DeleteObject acTable, strTableName
   
    'And then use the trusty old TransferDatabase method to re-import the table "Products" from the SQL Server :-)
    DoCmd.TransferDatabase acImport, "ODBC Database", strConnect, acTable, "LPID", strTableName

End Sub

Private Function TableExists(db As Database, strTableName As String) As Boolean
   
    Dim td As DAO.TableDef
    On Error Resume Next

    Set td = db.TableDefs(strTableName)
    TableExists = Err.Number = 0
   
End Function

I used it in a module and ran the CreateTable function off of a button on a form. I first ran it with no password in the connection string, only a username. After clicking the command button it was tied to a error message popped up that said it can't connect to the sql server, please enter your password. I clicked ok and then another form popped up with the server name, dB name, username, and a blank field where the password should go. I entered the correct password and clicked ok. After clicking ok the form disappeared and I checked my table roster to see if tblCurrentProducts was created (I didn't previously have this table in access). When I checked it out, sure enough there was the table with the correct information!

So, what this means to me is that, I can in fact connect to the sql server, now I just need to figure out how to run several different pass through queries on startup and update the information in the clients backend and then close the sql connection.

I'm going to try and create the pass through query again and see if it works. I'll post my results.

Try hard coding the connect string into the ODBC Connect Str property on the pass through to see if it works

Build the connect string and update all the querydef.connect = your connect string then run the pass throughs

Triple Post ftl...

Before trying a pass through query I thought I would try and see whether or not I had to have the connection string in the CreateTable function. I decided to make this function to see:

Public Sub CreateTableNoConnect()

    Dim strTableName As String, strConnect As String
       
    'What I want the table to be called
    strTableName = "tblCurrentProducts"
   
    'Check to see if table exists.  If it does, delete it.
    If TableExists(CurrentDb, strTableName) Then DoCmd.DeleteObject acTable, strTableName
   
    'And then use the trusty old TransferDatabase method to re-import the table "Products" from the SQL Server :-)
    DoCmd.TransferDatabase acImport, "ODBC Database", , acTable, "LPID", strTableName

End Sub

I then ran the CreateTable function which has the strconnect code. It ran fine of course. I then created another form with a command button that executed the CreateTableNoConnect function. It ran that with no problems! So, the connection was still open and is not needed to be declared every time (until of course the connection is closed). This was good news for me. I then went to the query builder and chose to create a passthrough query. The sql looked like this:

SELECT * FROM LPID;

I then tried to execute it. Immediately upon doing so the DSN box popped up! Asking for which DSN I wanted to use. I don't know why...and I don't know how to circumvent it.

Any suggestions?

For every query that you have populate the querydef.connect with your built string after you collect the user login and password then you'll get no popup.

So Jmoss,
Are all of your queries written in the VB editor?

I guess I'm just really confused as to how to run the pass throughs.

Lets say I create a global variable, that contains the connection info. Upon opening access they enter their UID and PWD. The global connection string now has all the info it needs. What is the next step I need to take? cnn.open? I have to actually connect to the sql database correct?

Most of my queries are written in code and depend on user selection criteria in a form. A vba module looks at whats populated in the form and builds the querydef.sql. The DSN less querydef.connect string is built when the main form opens. When the user clicks a button on the form, the vba builds the .sql and the pass through is executed. The pass through makes the connection upon execution

OK J
I think I've made some definite progress. I just finished testing a login form I made. It requests UID and Pass, after the user has entered that information and clicked "login" on a command button a string is created that connects to the server via oledb. It is working correctly, i.e. if the user enters incorrect information they are not logged in, but are asked to try and input the correct information. If the user enters the correct information then they are logged in and the frmLogin is made invisible. That way if I need to access the UID and PWD I can just do so by accessing the form's controls.

So, now the question is how do I make the pass through?

I just tried this with no luck:

'Make Database declarations
    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim strQueryName As String
    Dim qryDef As DAO.QueryDef
    Dim strConnect As String
   
On Error Resume Next
        'set variable values
            Set dbs = CurrentDb
            strQueryName = "qryGeneral"
            strConnect = Forms!frmLogin.ODBCConnect
        'Delete old query first - we want fresh data!
            dbs.QueryDefs.Delete strQueryName
       
            strSQL = "SELECT * FROM tblLPID;"
                                   
        'Create query definition
            Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
            qryDef.Connect = strConnect
        DoCmd.OpenQuery "qryGeneral"

This was tied to a command button, it was to run that upon being clicked. strConnect is a string that is is getting it's connection string directly from frmLogin. When I tried this, it did not work.

Any clues?

Ok, J

Scratch that. I didn't do anything different, just clicked the command button again and this time the query came up correctly. Odd... The one thing that I did notice is that I then disconnected from the sql server and then tried clicking the query button. The query came up correctly? So, does access cache some of what is being transfered?

Why delete the querydef? You could set it to empty but each time the .sql is updated the old .sql gets overwritten. If the same query is executed each time why bother recreating? If the same set of queries are generated each time why not just build SP instead?

The reason for deleting "qryGeneral" is because that query name is used for several different queries in my program. It's essentially just a "holding house" for multiple sql statements.

And you answered my next question with your question. I had seen on the net that several people just use stored procedures on their sql server and just have the pass through execute those. From what I've read this is also a faster, more efficient way of doing things as well so I'm going to try that next.

Thanks!

SPs are faster due to the fact  that an execution plan is saved on the server. All you have to do  in the pass through is EXEC mySP;

Well, I changed my code to this:

strSQL = "EXEC sp_LPID"

Yet it doesnt' run. BUT if, I make a passthrough query with that exact same sql, execute it (and choose a DSN that works) it executes fine. I don't understand why it would work for one, but not the other.

Does the user have correct permissions to execute the sp? I wouldn't want assume that if you had permission to do the pass through that you would have correct permissions for the SP. Basically, you should be using the same connect string, etc in the pass through for the SP as the one used for the SQL statement

I'm using the same user that I was using with the TSQL which happens to be the same user that created the sp. The statement I am using looks like this:

'Make Database declarations
    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim strQueryName As String
    Dim qryDef As DAO.QueryDef
    Dim strConnect As String
   
On Error Resume Next
        'set variable values
            Set dbs = CurrentDb
            strQueryName = "qryGeneral"
            strConnect = Forms!frmLogin.ODBCConnect
        'Delete old query first - we want fresh data!
            dbs.QueryDefs.Delete strQueryName
       
            strSQL = "EXEC sp_LPID"
                                   
        'Create query definition
            Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
            qryDef.Connect = strConnect
        DoCmd.OpenQuery "qryGeneral"

So the only difference is the execute statement.

Alright, I tried a few different ways and finally got it to work (I think my formatting else where was incorrect) but, never-the-less it's now working. Thanks for the help J!

I just took a sql statement from a user generated query and built an sp from it and also ran the sql statement from query analyzer. The query was a three column search of 8.5 million rows with a 2 column exact match (date) and the third column was wildcard on both ends of a vendor name. the query ran 38 seconds to return all rows, while the sp ran 40 seconds. 58000 rows returned

So it seems, the query was faster, but the speed difference was almost negligible.

I think that would be the majority type result unless you strung several more processes into the sp.