By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,994 Members | 2,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,994 IT Pros & Developers. It's quick & easy.

Creating A DSN using VBA

P: 38
Hi im using VBA to create A MYOB_ODBC User DSN

here is my code

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 'Constant Declaration
  3.       Private Const ODBC_ADD_DSN = 1        ' Add data source
  4.       Private Const ODBC_CONFIG_DSN = 2     ' Configure (edit) data source
  5.       Private Const ODBC_REMOVE_DSN = 3     ' Remove data source
  6.       Private Const vbAPINull As Long = 0  ' NULL Pointer
  7.  
  8.  
  9.       'Function Declare
  10.       #If Win32 Then
  11.  
  12.           Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
  13.           (ByVal hwndParent As Long, ByVal fRequest As Long, _
  14.           ByVal lpszDriver As String, ByVal lpszAttributes As String) _
  15.           As Long
  16.       #Else
  17.           Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
  18.           (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
  19.           lpszDriver As String, ByVal lpszAttributes As String) As Integer
  20.       #End If
  21.  
  22.  
  23.  
  24.  
  25.  
  26. Private Sub Command0_Click()
  27.       #If Win32 Then
  28.           Dim intRet As Long
  29.       #Else
  30.           Dim intRet As Integer
  31.       #End If
  32.       Dim strDriver As String
  33.       Dim strAttributes As String
  34.  
  35.       'Set the driver to SQL Server because it is most common.
  36.       strDriver = "MYOB_ODBC"
  37.       'Set the attributes delimited by null.
  38.       'See driver documentation for a complete
  39.       'list of supported attributes.
  40.       'strAttributes = "SERVER=SomeServer" & Chr$(0)
  41.       strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0) '' the Description
  42.       strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)         '' The DataSourceName
  43.       strAttributes = strAttributes & "DATABASE=C:\myob15\Clearwtr.MYO" & Chr$(0)
  44.       'strAttributes = strAttributes & "CFP=C:\myob15\Clearwtr.MYO" & Chr$(0)
  45.       'To show dialog, use Form1.Hwnd insteCreating Aad of vbAPINull.
  46.       intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
  47.       strDriver, strAttributes)
  48.  
  49.       If intRet Then
  50.           MsgBox "DSN Created"
  51.       Else
  52.           MsgBox "Create Failed"
  53.       End If
  54. End Sub
  55.  
  56.  
It creates the DSN fine, the problem is that i need to specify the database to use for the connection, but the MYOB_ODBC driver manual says that if a DSN is specified it will ignore the DATABASE Keyword.

Is there anyway around this issue so that i can specify the databse connection on the command0 click, or can this DATABASE Keyword be specified after the dns creation, please help as this is really holding up the project that i am working on
Apr 17 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.