asked on

VBA connection to sybase db

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

grant300

Have you tried hard coding the UID and PWD in the ODBC Data Source definition?

Also, getting an "HAxxxxxx" error is highly unusual. HA is High Availability so unless your site has the Sybase server you are going after setup with HA, you might want to look for something like incorrect or mixed versions of client library and ODBC drivers.

Regards,
Bill

slam69

ASKER

Thanks for the reponse bill, sybase has never been somethign ive had much contact with!!

I am aware how to code the uid and pwd within the VB above however i am unsure how i can do this as hard code if you could give me a nudge in the irght direction!! I can see within the odbc set up for that driver i can enter a default user name but not specify the password? is it possible to do this via registry or ini file amendment?

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slam69

ASKER

For reference, I entered the password as a variable witghin the registry this passed the password through and allowed teh script to run when no session present on the machine!