Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Connect Access to SQL Server

    I am totally new to using SQL Server. What I want to do is have a button on a form in Access which would link to a SQL Server database on a remote desktop and would run an SQL to bring data from certain tables into my database. How is this possible to do?

    Jez

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Instead of that, why not try linking tables with your access database?
    File > Get External Data > Link Tables
    Then select the bottom option in the dropdown for ODBC datasources
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks, but doesnt linking tables mean there is a permenant connection between the 2 database?
    The SQL Server db is only connectable if I am logged in on the VPN connection. I thought that using ADO it creates a connection and retrieves the tables I need and then breaks the connection. This is far more useful to me, as I may not be connected to the VPN at all times.

    Jez

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Jez - I have removed your duplicate post from the SQL Server forum. This is really an Access problem not a SQL Server one so you will get more joy in this forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    I am totally new to using SQL Server. What I want to do is have a button on a form in Access which would link to a SQL Server database on a remote desktop and would run an SQL to bring data from certain tables into my database. How is this possible to do?

    Jez
    Jez

    You have an open question in the Excel forum where MTB has written quite a lot of ADO code for you. The principle is pretty well the same here. You will need a different connection string to get the data from SQL Server.
    http://www.connectionstrings.com/?carrier=sqlserver
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    This below is my code that have written, but I keep getting an error message on the line highlighted with stars.

    The error is - 3704 - Operation is not allowed when the object is closed. what does this mean?

    Private Sub cmdImport_Click()
    Dim Conn As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim Comm As ADODB.Command

    Dim objConn As New ADODB.Connection
    Dim objRS As New ADODB.Recordset

    Dim strConn As String
    Dim strInsert As String
    Dim strSelect As String

    strConn = "Provider=sqloledb;Data Source=SLIDER\BISR;Initial" & _
    "Catalog=NameofSQLServer;User Id=myunsername;Password=mypassword"

    strSelect = "SELECT * FROM dbo.servicing_jobs"

    ********Set objRS = objConn.Execute(strSelect)*******

    Set Conn = New ADODB.Connection
    Set RS = New ADODB.Recordset
    Set Comm = New ADODB.Command

    Conn.Open strConn
    Comm.ActiveConnection = Conn
    Comm.CommandType = adCmdText

    Do While Not objRS.EOF
    strInsert = "INSERT INTO tblServicing"

    Comm.CommandText = strInsert
    Comm.Execute

    objRS.MoveNext
    Loop

    UpdateAwardErr:
    If Err.Number <> 0 Then
    MsgBox Err.Number & Err.Description
    Exit Sub
    Else
    MsgBox "Records were successfully inserted", vbInformation, "ServicingJobs Update"
    Exit Sub
    End If
    End Sub

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Blimey. That looks a bit of a mess from here. If I were you, start again with just the minimum number of objects and only use what you need. Build things up a step at a time. Open a connection and close it again. Next step, open the connection, assign it to a command object, assign command text and execute. Build it up slowly. You have loads of objects there with similar names that don't appear to do anything.

    Anyway - you create a connection string and a command string but assign them to nothing and then execute objConn which is
    1) closed
    2) does not have a command string
    3) Does not have a connection string

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Long story, but I cant access the remote server for the SQL database. So thought would have a practice at what you had said in your last thread and have ago.

    This is what I came up with, but for some reason, when I open the form in Access it doesnt pull through the data, Where have I gone wrong?

    Does it matter the database is on another PC, even though I have mapped the drive its on?

    Sub Initialise()
    On Error GoTo Err
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
    "Data Source=Z:\CSI\CSITables.mdb"
    sQRY = _
    "SELECT tblCSATAddress.BusinessType, Count(tblCSATAddress.JobNumber) AS Surveys, Count(IIf([inputflag]=True,1)) AS Responses, " & _
    "[Responses]/[Surveys] AS [Returns%], qryAvgNPS.AverageNPS, qryAvgCSAT.AverageCSAT " & _
    "FROM tblCSATAddress, qryAvgNPS, qryAvgCSAT " & _
    "WHERE tblCSATAddress.Contract <> '""' " & _
    "GROUP BY tblCSATAddress.BusinessType, qryAvgNPS.AverageNPS, qryAvgCSAT.AverageCSAT"
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenForwardOnly, adLockOptimistic

    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    Exit Sub
    Err:
    basError.LogError VBA.Err, VBA.Error$, "Form_fsubCSATStatusBar- Initialise()"
    End Sub


    Thanks, Jez

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    This is what I came up with, but for some reason, when I open the form in Access it doesnt pull through the data, Where have I gone wrong?
    Do you get an error?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    No Error message at all. Just a blank form that I had already set up.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    No Error message at all. Just a blank form that I had already set up.
    Ah - so you don't test the recordset? Remember - the form does not magically display the contents of the recordset. You need to assign it to the form's recordset.

    Also - you will need to persist it (as I recall). You can't go setting it to nothing and expect the data to stick around
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Not sure what you mean by persist it?

    I thought by running this query it would bring back the results?

    Or do you mean that I havent told the query where to show the results?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You populate a recordset ... and then destroy the recordset. You need to do something with the recordset to see the data. I suspect you want to set the form's recordset property to the recordset you have created.

    What you are attempting looks similar to something in this:
    http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx
    Search for "Assigning recordsets to controls" - the principle is the same it is just you are assigning the recordset to the form rather than a combobox.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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