Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Database List (2k3)

    I am trying to produce a list of the Databases in a Sql Server back end. I need the link to be dynamic because the server can change. I am using the following code and get a message "Type Mismatch" that I believe is occurring in the With QDF section. Can anyone help?

    Thanks,
    Tom

    Sub DB_Names()
    Dim dbsCurrent As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim sqlString As String

    sqlString = "ODBC;Driver={SQL Server};"
    sqlString = sqlString & "Server=" & Me.Server
    sqlString = sqlString & ";Database=Master;UID="
    sqlString = sqlString & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
    MsgBox sqlString
    Set dbsCurrent = CurrentDb
    Set qdf = dbsCurrent.CreateQueryDef("")
    With qdf
    .Connect = sqlString
    .SQL = "SELECT name FROM sysdatabases ORDER BY name"
    Set rst = .OpenRecordset()
    End With
    With rst
    Do While Not .EOF
    MsgBox rst!Name
    .MoveNext
    Loop
    End With

    rst.Close
    dbsCurrent.Close
    Set dbsCurrent = Nothing
    End Sub

  2. #2
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Database List (2k3)

    After a little more experimenting I discovered that the three Dim statements needed DAO added to them. So they should be :
    Dim dbsCurrent As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Once this change was made the routine worked as expected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •