Create an ODBC connection programmatically then AutoExec

[MsLady]

Can someone give me directions on how to Create an ODBC connection with vba code and AutoExec it on startup? Pleaseeeee?

 

[FoFa]

Check out this thread

 

[MsLady]

Check out this thread

Hi Fofa, Thanks for the link. But that's ASP method. How do i do this with VBA in access

 

[MsLady]

I have created this code in a new module:

Function RegSybase() As Boolean

Dim i As Integer

i = DSCreate("ODBC;DSN=PROJECTS;NLN=Winsock;NA=lnbsdb77,xxxx;DATABASE=xxxx;UID=xxxxx;PWD=xxxxx")

End Function

Then i created an autoexec macro, but when i run it i get Action Failed Error

Action Failed:
Macro Name: AutoExec
Condition: True
Action Name: RunCode
Arguments: RegSybase ()

What am i doing wrong?

 

[FoFa]

ry this (Needs to be tweaked).
Dim cn As New ADODB.Connection, rs as New ADODB.Recordset
DIM sql as string
'-------------------------------
' Create database connection string, login and password variables
'-------------------------------
Dim strConn, strLogin, strPassword
strLogin = "LoginID"
strPassword = "LoginPassword"
'-------------------------------
' Open the connection
'-------------------------------
cn.open "Provider=sqloledb;Driver={SQLServer};SERVER=SqlServerName;UID=" & strLogin & ";PWD=" & strPassword & ";Database=SqlServerDBname;"
'-------------------------------
' Connection end
'-------------------------------
' Create forward only recordset using current database and passed SQL statement
'-------------------------------
sql = "some sql goes here"
rs.Open sql, cn

 

[MsLady]

THanks fofa,
Got it. Here is what i have now

Private Sub Form_Load()
Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=LNP_SEC_LEND_PROJECTS;NLN=Winsock;NA=lnbsdb88,7344;DATABASE=slprojects;UID=sltuser;PWD=sltuser"
Set db = ws.OpenDatabase("", False, False, strConnection)



Call LogUser
End Sub