Results 1 to 3 of 3

Use a variable when connecting excel to access with vba

Threaded View

  1. #1
    Registered User
    Join Date
    04-04-2009
    Location
    Nashville, TN,USA
    MS-Off Ver
    Excel 2007
    Posts
    87

    Use a variable when connecting excel to access with vba

    I have had great success connecting to a access file through vba! However I am at a point where the column that I and querying could change. I would like some help altering me code. Thank you!

    Dim cnn As New ADODB.connection
        Dim rst As New ADODB.Recordset
        Dim companyid As String
        companyid = Application.Transpose(Range("myid").Value) & "P"
        
        cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" mydatasource"
    
    'This part works fine
        rst.Open "SELECT [" & companyid & "],[UM] FROM tblLineItems WHERE Item ='" & ListBox1.Value & "' ;", _
                 cnn, adOpenStatic
    
        rst.MoveFirst
        With Me
        .itemlbl.Text = ListBox1.Value
    'This part doesn't
        .pricelbl.Text = Format(rst! ["'" &companyid & "'"], "$0.00")
        End With
    '......
    I have also tried
    .pricelbl.Text = Format(rst!['companyid'], "$0.00")
    but it didn't work either

    See attached for the error message

    Just so you know if I don't use a variable in
    .pricelbl.Text = Format(rst![222193P], "$0.00")
    It works fine.

    So I really need to know how to put a variable in there!
    Attached Images Attached Images
    Last edited by rbyrd023; 02-23-2012 at 12:17 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1