Results 1 to 4 of 4
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Variables In VBA

    I have already declared .Item("Data Source") now how can I extract the value that is stored? For example, this syntax produces an error of "Operation is not allowed when the object is closed
    Code:
    Dim c As ADODB.Connection
    Set c = New ADODB.Connection
    With c
        .Provider = "sqloledb.1"
        With .Properties
            .Item("Data Source") = "Server"
            .Item("Initial Catalog") = "Database"
            .Item("PassWord") = "password"
            .Item("User ID") = "user"
        End With
        Debug.Print .Item("Data Source")
    	Debug.Print c.Item("Data Source")
    'More syntax below
    So I tried to move the Print statement before the End With and I got the same error
    Code:
    Dim c As ADODB.Connection
    Set c = New ADODB.Connection
    With c
        .Provider = "sqloledb.1"
        With .Properties
            .Item("Data Source") = "Server"
            .Item("Initial Catalog") = "Database"
            .Item("PassWord") = "password"
            .Item("User ID") = "user"
            Debug.Print .Item("Data Source")
    	Debug.Print c.Item("Data Source")
    
        End With
    'More syntax below
    I know I can take the easy road, and just declare the variables again like this
    Code:
    Dim conn As String
    Set conn = "Server Name"
    But then I fail to learn something new! How can I pull out the information from .Item

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    now that its inside the with, you may need a property of the item, like:
    .Item("Data Source").value

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by ranman256 View Post
    now that its inside the with, you may need a property of the item, like:
    .Item("Data Source").value
    Is it possible to access the value outside of the .with block?

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    This does not compile but it shows what I am after. I am essentially wanting to iterate tables on a SQL Server database to DSNLess link into an acacess database and pass in the following parameters
    Code:
        stLocalTableName
        stRemoteTableName
        pkfield
        stServer
        stDatabase
        stUsername
        stPassword
    This is the syntax, I was wanting to use (I know this does not compile so it will require some work) but illustrates the point
    Code:
    Public Sub GetTableNames()
    Dim c As ADODB.Connection
    Dim r As ADODB.Recordset
    Dim f As ADODB.Field
    Dim conn As String
    Set c = New ADODB.Connection
    With c
        .Provider = "sqloledb.1"
        With .Properties
            .Item("Data Source") = "Server"
            .Item("Initial Catalog") = "Database"
            .Item("PassWord") = "password"
            .Item("User ID") = "user"
        End With
        .Open
        Set r = .OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
        With r
            While Not .EOF
                If (.Fields("TABLE_TYPE") = "TABLE" And .Fields("TABLE_NAME") Like "Test*") Then
                    Debug.Print .Fields("TABLE_NAME")
                    Debug.Print Mid(.Fields("TABLE_NAME"), InStr(1, .Fields("TABLE_NAME"), "_") + 1)
                    AttachDSNLessTable (Mid(.Fields("TABLE_NAME"), InStr(1, .Fields("TABLE_NAME"), "_") + 1), .Fields("Table_Name"), .Item("Data Source").Value, .Item.("Initial Catalog").Value, .Item("User ID").Value, .Item("PassWord").Value))
                End If
                .MoveNext
            Wend
        End With
    End With
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Public variables
    By jmitchelldueck in forum Programming
    Replies: 5
    Last Post: 08-20-2015, 12:03 PM
  2. Hyperlink Variables
    By CCW8 in forum Access
    Replies: 5
    Last Post: 07-08-2013, 06:20 PM
  3. VBA variables in SQL
    By compooper in forum Programming
    Replies: 3
    Last Post: 07-06-2011, 11:04 AM
  4. VBA in variables
    By smikkelsen in forum Access
    Replies: 3
    Last Post: 11-12-2010, 03:14 PM
  5. sql in vb variables
    By emilylu3 in forum Programming
    Replies: 3
    Last Post: 03-04-2006, 01:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums