Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problems (Excel 97 & Access 97)

    I am trying to import data from Access 97 into my Exel 97 spreadsheet. I keep coming up with this error:
    ODBC Driver manager - Data Source not found & no Defined drivers specified

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Query problems (Excel 97 & Access 97)

    Try saving your Access information as an Excel spreadsheet.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problems (Excel 97 & Access 97)

    I thought of that.. but the people that will be using this want it saved to an excel spreadsheet.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problems (Excel 97 & Access 97)

    This is nor clear. You are saying that "the people that will be using this want it saved to an excel spreadsheet" which is exactly what Catharine is suggesting. If you or your client are in EXCEL and you want to retrieve data frm ACCESS, why not use MSQuery? It works like a charm when you have the ODBCs set up correctly.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problems (Excel 97 & Access 97)

    This post has some VBA code for accessing an Access database from and Excel spreadsheet, and populating the spreadsheet- if you're interested in going that way.

    MS Query may well be the better way to go. I include this reference just in case- and to show the code isn't that long.

    You would need a reference to "Microsoft ActiveX Data Objects" (Project, References, and select the appropriate box)) though- you may not have that with Excel 97.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problems (Excel 97 & Access 97)

    This would be an eaiser way of doing things. THe client/use WANT to go into Excel and do this. After we showed them how it looks this way they aggreeed to try it out.

    Can anyone help me here? How can I run a vb script from outllook that will run an access97 macro. I also want it to add a to line, a subject line, and information into the body before the attached spreadsheet.
    Right now I am brain dead with other problems I am having over the weekend.

    Any help is greatly appricated. If you know anyone that can build an SQL server or serve as a consultant and are in the Houston, Tx. area please ask them to drop a line or call me.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problems (Excel 97 & Access 97)

    I have a command button on my outlook form that I use to save the data to an Access db.
    '-=-=-=-=-=-=-=-=-=-=-=-
    Sub Update2()
    Dim Obe
    Dim MyDB
    Dim Rst
    Dim Rsm

    'On error resume next
    Set Dbe = Application.CreateObject("DAO.DBEngine.35")
    If Err.Number <> 0 Then
    MsgBox Err.Description & "--- Some functions may not work correctly" _
    & Chr(13) & "Please make sure that DAO 3.5 is installed on this machine"
    Exit sub
    End If

    Set MyDB = Dbe.Workspaces(0).OpenDatabase("tabsproddatabaseed garSecuritysecurity2.mdb")
    RequestNum = UserProperties.Find("jobnum").Value
    Set Rst = MyDB.OpenRecordset("select * from termination where jobnum = " & RequestNum)

    If Rst.EOF = True And Rst.BOF = True Then
    Set Rst = MyDB.OpenRecordset("termination")
    rst.addnew
    Else
    rst.edit
    End if

    ' Access side Outlook side
    Rst.fields("jobnum") = UserProperties.Find("Jobnum").Value
    Rst.fields("termname") = UserProperties.Find("termname").Value
    Rst.fields("termdate") = UserProperties.Find("termdate").Value
    Rst.fields("posting") = UserProperties.Find("posting").Value
    Rst.fields("badges") = UserProperties.Find("badges").Value
    Rst.fields("hrsig") = UserProperties.Find("hrsig").Value
    Rst.fields("hrdept") = UserProperties.Find("hrdept").Value
    Rst.fields("hrhiredate") = UserProperties.Find("hrhiredate").Value
    Rst.fields("jobtitle") = userProperties.Find("jobtitle").Value

    Rst.update
    Rst.close
    MyDB.Close

    End Sub
    '-=-=-=-=-=-=-=-=-=-=-=-=-

    From this point the next step in my command button is to start the Excel spreadsheet. Instead of doing this. How can run an Access macro to run my query request and save the output to a spreadsheet and get it ready for mail out?

    '-=-=-=-=-=-=-=-=-=-=-=-=-
    'Call Excel
    Sub cmdExcel_click()
    update2
    Set objWSHShell = CreateObject("WScript.Shell")
    objWSHShell.Run("excel.exe tabsproddatabaseedgarsecurityterm2001.xls ")
    end sub
    '-=-=-=-=-=-=-=-=-=-=-=-=-

Posting Permissions

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