[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Temporary table and recordset connection problem.

Posted on 2005-04-13
6
Medium Priority
?
336 Views
Last Modified: 2010-05-02
Here is what I'm trying to do:
1. I want to create a temporary table
2. fill that temporary table with data from an excel file
3. Then create a recordset from that temporary table.

The problem is that I'm actually coding this in an automation tool that uses VBA as its IDE, so I can't setup a connection to any database on the open recordset command. i.e. rst.open Source, Connection, CursorType, LockType, Options.  

What do I use as a connection when I just want the data in the temporary table.  Is there another way to get the data from the temporary table into a recorset without a connection defined?
0
Comment
Question by:Ecircle
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 13775275
Here's code for opening a excel worksheet as an ADO recordset.  I use this from the Excel VBA IDE.

Dim Folder As String, File As String, stCon As String, stSQLNumberRows
Dim stRows As String, stSQL As String, Count As Long
Dim cnt As ADODB.Connection, rst As ADODB.Recordset

stSQLNumberRows = 10
Folder = "C:\Test"
File = "Test.xls"

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
        stCon = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "ReadOnly=1;DBQ=" & Folder & "\" & File

        cnt.Open stCon
        Set rst = cnt.Execute("[A1:A100]") 'Can also use a named range for a worksheet other then index 1.
        ActiveSheet.Range("A65535").End(xlUp).Offset(1, 0).CopyFromRecordset rst

Set rst = Nothing
Set cnt = Nothing

HTH
Cal
0
 
LVL 1

Author Comment

by:Ecircle
ID: 13775565
Ok, this is funny...

You actually answered my question yesterday and this worked for the first part of what I was trying to do becuase the data was very uniformed and didn't have headings or anything.  I not sure that it will work for the second excel file because thet data is not uniform and I need to distinguish column heading for my data.  Meaning the data looks like this:
Level  Sequence   Action  Keyword
BS
BG
BN
T         1              Enter     Alpha
EN
EG
ES


using the code you gave me, how can I say that I want the Level, Sequence, Action, and Keyword to be Column headings for my data in my recordset.  That way I can put criteria against it in the recordset. i.e. SELECT * FROM temptblData Where Action = "Enter"
open recordset...
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 13775668
Thought the question looked familiar.

What about creating 4 seperate Recordsets based on the same range, with different columns, Cycle through each recordset at the same interval?  I will whip up some code and post it after this.

Cal
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Ecircle
ID: 13775864
Sounds good...thanks for the help.
0
 
LVL 16

Accepted Solution

by:
Calvin Brine earned 2000 total points
ID: 13776409
WOW, that was more difficult than I thought.  It seems to display blank cells as null's, so you need to watch out for that.  I setup my test file with the same format as yours, and used the index value in the recordset, becase it bypasses the first line(Mostly likely assigns them to the recordset name, didn't test that though, so use the index or test it).  

Private Sub CommandButton1_Click()
Dim Folder As String, File As String, stCon As String, stSQLNumberRows
Dim stRows As String, stSQL As String, count As Long
Dim cnt As ADODB.Connection
Dim LevelRst As ADODB.Recordset

Folder = "C:\Test"
File = "Test.xls"

Set cnt = New ADODB.Connection
Set LevelRst = New ADODB.Recordset
Set SeqRst = New ADODB.Recordset
Set ActionRst = New ADODB.Recordset
Set keyRst = New ADODB.Recordset

On Error GoTo ErrHandler

    stCon = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
    "ReadOnly=1;DBQ=C:\" & File

    cnt.Open stCon
    Set LevelRst = cnt.Execute("[A1:d100]") 'Can also use a named range for a worksheet other then index 1.
           
LevelRst.MoveFirst


       
Do Until LevelRst(0) = ""
    If Not (IsNull(LevelRst(0))) Then MsgBox LevelRst(0)
    If Not (IsNull(LevelRst(0))) Then MsgBox LevelRst(1)
    If Not (IsNull(LevelRst(0))) Then MsgBox LevelRst(2)
    If Not (IsNull(LevelRst(0))) Then MsgBox LevelRst(0)
   
    LevelRst.MoveNext

Loop

Set LevelRst = Nothing
Set cnt = Nothing
Exit Sub

ErrHandler:
Select Case Err.Number

Case 3021
    MsgBox "Complete"

Case Else
    MsgBox Err.Number & " " & Err.Description
End Select
End Sub

HTH
Cal
0
 
LVL 1

Author Comment

by:Ecircle
ID: 13776889
This looks great, thanks for the hard work!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question