Results 1 to 9 of 9
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Get Data from SQL Server (VBA/Excel/2000 SR1)

    We have an Access database that was just upsized to SQL Server 2000. We have had an Excel VBA routine that takes raw data, moves it around and in a final step went to Access to bring back some data. Here is my old code:
    <pre>Dim wrkJet As Workspace
    Dim dbsJobData As DAO.Database
    Dim rstTemp As DAO.Recordset

    Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "", dbUseJet)
    Set dbsJobData = OpenDatabase(DataPath & "JobData.mdb", False)</pre>

    I have created an ODBC link to the new place on DATABASE (imaginative names from the administrator) and the actual database is SalesCommission and the table is
    Alan

  2. #2
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    I got past that little thing. Figured out that the guy that established my database on the SQL Server misspelled Commissions (one "s"). When I get over to the next hurdle,
    <pre>Private Sub GetFieldData(DataObject As cDataRow, _
    DataFile As DAO.Database, RecSet As DAO.Recordset)</pre>

    that was used before the server change, I do not think it should be DAO.Database but cannot figure what I need.
    Alan

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    I've never played with DataObjects, so I can't be much help, but you might want to take a look at ODBCDirect in on-line help. That's what we use for dealing with a SQL Server backend with linked tables.
    Charlotte

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    Is there a reason you aren't using a permanently linked table, rather than trying to create a connection on the fly? If you have a permanent connection established using the linked table manager, then you can treat the SQL Server table as you would a linked Access table in 99.9% of the cases.
    Wendell

  5. #5
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    I do not think I can use a permanently linked table in Excel. {Not sure why my post was moved to the Access forum}.

    I went back to the server administrator and suggested we set the DB for Windows authenication so the password does not have to be in the VBA.

    Here is where the code (in bold) blows up now:
    <pre>Private Sub GetFieldData(DataObject As cDataRow, DataFile As DAO.Database, _
    RecSet As DAO.Recordset)
    Dim cel As range
    Dim strSQL As String
    Dim strFormat As String

    strSQL = "SELECT * from LastMonth WHERE LastMonth.JobNo='" & _
    DataObject.JobNumber & "' AND Lastmonth.YearMonth='" & strYearMonth & " ';"
    Set RecSet = DataFile.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

    If RecSet.RecordCount = 0 Then
    GoTo ExitSub
    End If


    ' Move to the selected record
    RecSet.MoveFirst
    </pre>

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    Your question wound up here because this is where SQL Server questions lurk since they don't have a home of their own, poor things. As for why your code is blowing up, you declare DataFile as a database object, but you never set it to point at a database, which is why that line of code blows up.

    What do you have selected as the data source for this external data and what driver are you using? Working with SQL Server is a little different (all right, a LOT different) from working with an Access database.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    It isn't exactly a permanently linked table as it would be in Access. When you import the data from SQL Server, you create an external data range and you can specify the properties of that data range. This works much like a linked table and exactly like links to an Access database or some other data source. You can also save the query used to get the data, and that might be the best way to open your recordset, although I can't say I've ever tried it.
    Charlotte

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    I've mostly forgotten DAO. Does it return error information like ADO? Could you elaborate on the error you get for the blowing up part?

    (Also, I find it a bit mysterious that you are Set-ing a variable that is passed as a parameter. That isn't illegal, of course, so I don't think that's the problem, but I find it confusing.)

  9. #9
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Get Data from SQL Server (VBA/Excel/2000 SR1)

    My predecessor at this company wrote the original extract about 4 years ago. Following his code and logic has been an adventure. <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    I am about ready to junk all of the offending code and write my own extract from SQL Server. Thank you to everybody that tried to help.
    Alan

Posting Permissions

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