Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: Linking SQL Server to Access MDB File

    Hi,

    I am using an MDB file to talk to a SQL Server. I would like to use pass-through queries to keep as much of the processing on the server as possible. However, I have been told that the results of such queries will be read-only. How do I keep the processing on the server but generate read-write answers to my queries?

    Thanks
    Matt

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Linking SQL Server to Access MDB File

    How about using ADO with an ADO connection and recordset object?

    You select the characteristics of the recordset like CursorLocation (choose adUseServer), the CursorType (use Static), and LockType (don't choose ReadOnly) and that should work. There is a company that makes a free download that does this easily: http://www.scirocco.ca
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Talking Re: Linking SQL Server to Access MDB File

    So if I choose adUseServer all of the processing will be done on the server, and the results returned to Access?? Will this still work when using a SELECT WHERE statement that passes a parameter from a form e.g. CustomerID. I'd prefer to write my own code as opposed to using the dowload, do you have some example I could look at? :-)

    Thanks

    Originally posted by rami.haddad
    How about using ADO with an ADO connection and recordset object?

    You select the characteristics of the recordset like CursorLocation (choose adUseServer), the CursorType (use Static), and LockType (don't choose ReadOnly) and that should work. There is a company that makes a free download that does this easily: http://www.scirocco.ca

  4. #4
    Join Date
    Jan 2004
    Posts
    184

    Re: Linking SQL Server to Access MDB File

    Yes you send the query to SQL all the processing will be done on the server and then it will return the results to your recordset. Here is an example:

    dim cn as ADODB.connection
    dim rs as ADODB.recordset

    dim floatAverage as float

    set cn = new ADODB.connection

    ' With Integrated Security
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDataBaseName;Data Source=ServerName"

    ' Using a UserName Password
    Pcn.ConnectionString = "rovider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=TestDataBaseName;Data Source=ServerName"

    cn.open 'Now you have an open connection to SQL

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn
    rs.CursorLocation = adUseServer
    rs.CursorType =
    rs.LockType

    rs.open "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM TABLE WHERE Item_Nbr=' " & textbox.text & " ' "

    rs.close
    cn.close

    set rs=nothing
    set cn = nothing
    In abundance of water only the fool is thirsty. Bob Marley.

  5. #5
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Re: Linking SQL Server to Access MDB File

    That's exactly what I was looking for, thanks. Does the SQL syntax have to be Transact SQL or will Access SQL work as well?

    Originally posted by rami.haddad
    Yes you send the query to SQL all the processing will be done on the server and then it will return the results to your recordset. Here is an example:

    dim cn as ADODB.connection
    dim rs as ADODB.recordset

    dim floatAverage as float

    set cn = new ADODB.connection

    ' With Integrated Security
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDataBaseName;Data Source=ServerName"

    ' Using a UserName Password
    Pcn.ConnectionString = "rovider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=TestDataBaseName;Data Source=ServerName"

    cn.open 'Now you have an open connection to SQL

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn
    rs.CursorLocation = adUseServer
    rs.CursorType =
    rs.LockType

    rs.open "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM TABLE WHERE Item_Nbr=' " & textbox.text & " ' "

    rs.close
    cn.close

    set rs=nothing
    set cn = nothing

  6. #6
    Join Date
    Jan 2004
    Posts
    184

    Re: Linking SQL Server to Access MDB File

    No problem, since you are sending the query to SQL Server you must use SQL Server syntax.
    In abundance of water only the fool is thirsty. Bob Marley.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •