#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2004
    Posts
    71
    Rep Power
    10

    Connecting to a Progress Database through an Access Database


    Hi guys , I've been writing some fairly complex db queries in MS Access connecting to our Progress datasource using ODBC.

    These work fine but I'd really like to display the results on our company intranet which I built using classic ASP connecting to an Access database.

    When I try to display the results of a query I get the following error messages

    Code:
    Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
    
    [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'livesxe' failed. 
    
    /charts/sales_figures.asp, line 28
    Can anyone help ?

    Cheers

    Andy
  2. #2
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jul 2005
    Location
    Oxford UK
    Posts
    3,897
    Rep Power
    989
    hi
    can u post the code here.and mark line no 28.

    cheers!!
    Nothing is Impossible bcoz IMPOSSIBLE itself says..
    I M POSSIBLE........................
    Be cool !!!!!!!!
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2004
    Posts
    71
    Rep Power
    10
    Code:
    <%
    
    
    Dim adCon
    Dim strSQLCounter
    Dim rsNews
    Dim strSQL
    Dim lnkID
    DIM iMonth
    iMonth = Month(now)
    
    lnkID = CLng(Request.QueryString("ID"))
    
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:\Intranet\new intranet\rbstar.mdb"
    
    strSQLCounter = "Insert Into Counter(counter_name, Counter_page, counter_level  ) Values('Monthly Sales Figures', 'charts/sales_figures.asp' , 1);"
    adoCon.Execute(strSQLCounter)
    
    
    Set rsNews = Server.CreateObject("ADODB.Recordset")
    
    strSQL = "SELECT Sum([Sales Analysis].[Invoice Amount]) AS [SumOfInvoice Amount], Sum([Sales Analysis].[Margin £]) AS [SumOfMargin £], [Sales Analysis].[Sales Rep], Employee.employee_ID, Employee.Employee_first_name, Employee.Employee_surname " &_
    " FROM [Sales Analysis] LEFT JOIN Employee ON [Sales Analysis].[Sales Rep] = Employee.Employee_sales_initials " &_
    " GROUP BY [Sales Analysis].[Sales Rep], Employee.employee_ID, Employee.Employee_first_name, Employee.Employee_surname " &_
    " ORDER BY Sum([Sales Analysis].[Invoice Amount]) DESC; "
    rsNews.Open strSQL, adoCon
    
    %>
    Line 28 is

    Code:
    rsNews.Open strSQL, adoCon
    Essentially there are several queries which build up the sales figures from the Progress datasource. All I've done is taken the last query in Access which displays the final results and try to use a web front end to display the data.

    Thanks for your quick reply

    Andy
  6. #4
  7. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jul 2005
    Location
    Oxford UK
    Posts
    3,897
    Rep Power
    989
    hi
    first tell me your insert code is working i meant record is inserting into your table.

    lets debug this put bold part in your code .this code will print your query on the browser copy that code and run that code into your access database query analzer.

    see r u getting any error.and post your printed query and error here.

    Code:
    strSQL = "SELECT Sum([Sales Analysis].[Invoice Amount]) AS [SumOfInvoice Amount], Sum([Sales Analysis].[Margin £]) AS [SumOfMargin £], [Sales Analysis].[Sales Rep], Employee.employee_ID, Employee.Employee_first_name, Employee.Employee_surname " &_
    " FROM [Sales Analysis] LEFT JOIN Employee ON [Sales Analysis].[Sales Rep] = Employee.Employee_sales_initials " &_
    " GROUP BY [Sales Analysis].[Sales Rep], Employee.employee_ID, Employee.Employee_first_name, Employee.Employee_surname " &_
    " ORDER BY Sum([Sales Analysis].[Invoice Amount]) DESC; "
    Response.write strSQL
    Response.end
    rsNews.Open strSQL, adoCon
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2004
    Posts
    71
    Rep Power
    10
    The code runs fine in access and gives the results shown below.

    I don't have any insert queries running at the moment , I did try to run an insert query through ASP but got the same error message.

    Infact any query no matter how simple gives me the same error message cannot connect to the progress datasource...

    I don't understand it as all i'm doing is connecting the access database which i would have thought would do the ODBC to get the data from the progress datasource ....

    SumOfInvoice Amount SumOfMargin £ Sales Rep employee_ID Employee_first_name Employee_surname
    24230.24 4330.75 SRB 139 Simon Brooker
    22303.06 5176.81 TF 13 Terry Foyle
    21004.28 5225.04 PF 12 Paul Finnis
    19065.35 3255.11 ih 104 Ian Huggins
    17219.03 3261.36 JH 101 John Harding
    12150.09 3799.91 DB 102 Daren Busby
    11788.01 2733.85 DF 90 Darrell Francis
    9943.76 2329.31 MJ 142 Michael Jarman
    9735.35 1217.96 PL 141 Pierre Labiche
    6396.08 1553.08 dis
    6066.43 1831.14 mm 21 Mark Mitchell
    5912.55 1698.53 ds 28 Darren Snow
    4480.88 1624.82 ECOM
    4203.4 1605.57 RDS 111 Ricky Simmonds
    3442.85 1179.4 TC 36 Tony Curtis
    2440.05 789.95 rk 43 Rick Kingsnorth
    1923.76 675.6 ms
    1704.75 934.53 SC 140 Sajive Chellappan
    1388.53 1003.42 RJS 112 Robert Saw
    1292.88 467.76 BL 60 Billy Lee
    1229.26 221.25 DR 56 Domenic Raffa
    1037.72 187.41 CM 18 Cas Mitchell
    914.46 267.89 aps 37 Andrew Sharpe
    878.62 169.69 BB 39 Ben Beeson
    624.32 216.4 cb 96 Christopher Beard
    621.06 179.11 dc 118 David Cornish
    357.58 84.35 SB 8 Stuart Burbidge
    187.11 81.55 DA 109 Dave Alexander
    125.85 40.98 SL 63 Stephen Lane
    1.01 0.04 RS 29 Roger Snow
  10. #6
  11. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jul 2005
    Location
    Oxford UK
    Posts
    3,897
    Rep Power
    989
    hi
    instead of odbc use oledb for connection.change data source as per your database name with path.
    Code:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=;Password=;"

Similar Threads

  1. PLEASE HELP: Access Database Problem (using Frontpage)
    By new2this in forum Microsoft Access Help
    Replies: 0
    Last Post: March 10th, 2006, 09:08 AM
  2. Same DB plays different with XP and W2K
    By datasurf in forum Microsoft Access Help
    Replies: 3
    Last Post: June 2nd, 2005, 11:41 PM
  3. Securing A Database
    By TBÁrpi in forum Microsoft Access Help
    Replies: 31
    Last Post: January 25th, 2005, 05:23 PM
  4. error connecting asp with access database
    By metale in forum Code Bank
    Replies: 2
    Last Post: October 18th, 2004, 04:13 PM
  5. Problem connecting to Access database
    By Klas in forum ASP Development
    Replies: 2
    Last Post: March 26th, 2004, 08:59 AM

IMN logo majestic logo threadwatch logo seochat tools logo