Convert VB to VBA

[Jo15765]

How would this procedure be converted to VBA?

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
    (ByVal hwndParent As Long, ByVal fRequest As Long, _
    ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
    Public DB As ADODB.Connection
    Public connString As String
 
Public Sub Connect()
    Call Create
    
    connString = "DSN=VRRS;"
    Set DB = New ADODB.Connection
    DB.Open connString
End Sub
 
Private Function CreateAccessDSN(DSNName As String, DatabaseFullPath As String) As Boolean
    Dim sAttributes As String
    sAttributes = "DSN=" & DSNName & Chr(0)
    sAttributes = sAttributes & "DBQ=" & DatabaseFullPath & Chr(0)
    CreateAccessDSN = CreateDSN("Microsoft Access Driver (*.mdb)", sAttributes)
End Function
 
Private Function CreateDSN(Driver As String, Attributes As String) As Boolean
    CreateDSN = SQLConfigDataSource(0&, 1, Driver, Attributes)
End Function
 
Private Sub Create()
    Dim blnRetVal As Boolean
    blnRetVal = CreateAccessDSN("VRRS", "C:\VRRS\VRRS.mdb")
End Sub

What I am trying to do is use VBA to set up a DSN on my PC

[Spoo]

Jo

If you use exactly that code as a macro on an Excel sheet (ie, VBA) ,,,
.. does it work?
.. does it crash? If so, at what line?

Spoo

[Jo15765]

I could be totally wrong, but it seems to me this was creating an ACCESS DSN, and I am needing a SQL Server DSN. However, to answer your questions, the Compile errors I get is:
Compile Error:
User-Defined type not defined
and these are the 2 lines that produces that error:

    Public DB As ADODB.Connection
    Set DB = New ADODB.Connection

[westconn1]

add a reference to ADO

[Jo15765]

add a reference to ADO

I added a reference to Microsoft ADO Ext. 6.0 for DDL and Security maybe that's the wrong one?

EDIT -- and it is Excel 2007

[westconn1]

i guess i would be using ADO ver 2.8 library, but get confused with the different versions used for later versions of windows

[Jo15765]

Correct me if I am wrong, but I am looking for the Object Library right?

If that statement is true, I do not have a ADO object library available as a reference...I only have the DAO
Microsoft DAO 3.6 Object Library

[Spoo]

Jo

DAO is an older Access feature, but still might work.
I still used DAO for VB6 apps (. .) ,, but have not tried it in VBA.

Spoo

[Jo15765]

How would the code be converted from ADO to DAO?

[Spoo]

Jo

I'm not familiar with a DSN, so I can't help you there.

If you are only trying use Excel to open and read from Access, then maybe
I might be able to help. I'll await your reply.

Spoo

[Jo15765]

I am wanting Excel to create a DSN for SQL Server.

[westconn1]

add a reference to ADO

microsoft activex data objects, any system from win 98 should have available, maybe different in w7 or w8

afaik you do not have to create a DSN, just use the correct connection string, check out connectionstrings.com
the code to create a DSN should work when you have a reference to ADO

[Jo15765]

So if I just create a connection within the workbook, I will not have to create a system DSN to allow the users to be able to refresh the queries in the workbook?

[Spoo]

Jo

What is a system DSN?

To just read an Access mdb, perhaps something like this

Sub ReadMyAccess()
    '
    Dim DB1 As Database
    Dim RS1 As Recordset
    '
    mymdb = "c:\access\SomeMDB.mdb"
    myrs = "SomeRS"
    '
    Set DB1 = OpenDatabase(mymdb)
    Set RS1 = DB1.OpenRecordset(myrs)
    '
    v1 = RS1.RecordCount
    RS1.MoveFirst
    For ii = 1 To 6
        v2 = RS1("date")
        v3 = RS1("day")
        RS1.MoveNext
    Next ii
End Sub

I added these Tools>References

,, Microsoft Access 11.0 Object Library
,, Microsoft DAO 3.6 Object Library

Spoo

[westconn1]

I added these Tools>References

,, Microsoft Access 11.0 Object Library
,, Microsoft DAO 3.6 Object Library

the first would require access to be installed and DAO should be avoided with any other database than access, even with access it is better to use ADO now

[Spoo]

the first would require access to be installed and DAO should be avoided with any other database than access, even with access it is better to use ADO now

Agreed, but OP indicated that an ADO reference was not an available reference.
So, are you suggesting dropping the first one and just keeping the second?

[westconn1]

So, are you suggesting dropping the first one and just keeping the second?

no, the first may not be available if access is not installed and he indicated it was not an access database, but sql server

i suggest he finds why ADO is not available, if he is using w7 or w8 x64, it may well be a different driver