troubleshooting Question

VBA connection to sybase db

Avatar of slam69
slam69Flag for United Kingdom of Great Britain and Northern Ireland asked on
Sybase DatabaseVisual Basic ClassicVB Script
4 Comments1 Solution1881 ViewsLast Modified:
Apologies but a new job for me means trying to get my brain going again.

i am running a vba script to append tables in Access 2000, see below, however it links to a sybase source, when the table is re-queried the script hangs waiting on the password.

i have tried to get round this by sendkeys but thsi wont work as it wont move teh vba onto the next line and I am now trying to do it using a connetion string to open the connection before the table is queried however I get the ct_connect: hafailover error message, I am guessing due to incorrect parametres in teh connection string.

please can someone help!!

Function MCR_Append_tables_query()
On Error GoTo MCR_Append_tables_query_Err

    ' Set to No
    DoCmd.SetWarnings False
    ' Daily Application Stats
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "ODBC;Driver=SYBASE ASE ODBC Driver;DSN=iccm_preview;srvr=iccm_preview;db=blue;UID=dunks;PWD=bsupport1"

   DoCmd.OpenQuery "qryMakeTableDaily Application Stats", acNormal, acReadOnly
            With conn
 End With
   
   
    ' Daily CDN Stats
    DoCmd.OpenQuery "qryMakeTableDaily CDN Stats", acNormal, acEdit
    ' Interval Application Stats
    DoCmd.OpenQuery "qryMakeTableInterval Application Stats", acNormal, acReadOnly
    ' Interval CDN Stats
    DoCmd.OpenQuery "qryMakeTableInterval CDN Stats", acNormal, acReadOnly
    ' Weekly Application Stats
    DoCmd.OpenQuery "qryMakeTableWeekly Application Stats", acNormal, acEdit
    ' Weekly CDN Stats
    DoCmd.OpenQuery "qryMakeTableWeekly CDN Stats", acNormal, acReadOnly
    ' Update Blank fields in Interval CDN Table
    DoCmd.OpenQuery "qryUpdatelInterval CDN Stats", acNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.Quit acSave


MCR_Append_tables_query_Exit:
    Exit Function

MCR_Append_tables_query_Err:
    MsgBox Error$
    Resume MCR_Append_tables_query_Exit
ASKER CERTIFIED SOLUTION
grant300

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros