Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Unanswered: recordset problem

    I want to select data from an ODBC-Source

    SELECT * FROM table

    This runs perfect. But I have x tables

    I want to select all data from x tables

    and the table structure is different.

    Can i only access the recordset with rs![name]
    or is there a method to access the first column of the row and then the second and so on
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: recordset problem

    Originally posted by eperich
    I want to select data from an ODBC-Source

    SELECT * FROM table

    This runs perfect. But I have x tables

    I want to select all data from x tables

    and the table structure is different.

    Can i only access the recordset with rs![name]
    or is there a method to access the first column of the row and then the second and so on
    Im not sure what your asking, do you want to select all data from all tables at the same time?

    Also look up querydef, its a tad bit more user frendly than recordsets

    Jim

  3. #3
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    querydef

    The problem is this is an import script and I want to import the table structure and the data

    I have the table structure and I want to SELECT the data from the scourcedestination and want to insert it in the table

    The status at the moment is:

    For Each feld In rsdata.Fields
    tmpwert = feld.Name
    Debug.Print "Name: " & feld.Name & "--- Daten: " & rsdata![tmpwert]
    Next

    The variable "tmpwert" has the fieldname in it
    and i want to get the data from the recordset of this field

    Is this possible?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: querydef

    Originally posted by eperich
    The problem is this is an import script and I want to import the table structure and the data

    I have the table structure and I want to SELECT the data from the scourcedestination and want to insert it in the table

    The status at the moment is:

    For Each feld In rsdata.Fields
    tmpwert = feld.Name
    Debug.Print "Name: " & feld.Name & "--- Daten: " & rsdata![tmpwert]
    Next

    The variable "tmpwert" has the fieldname in it
    and i want to get the data from the recordset of this field

    Is this possible?
    What is the original data source, because if they are linked tables then you can do append querys to the table structure that you ahve in place, and save all of the recordset stuff alltogether.
    Jim

  5. #5
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    source

    The data source is a postgresql database on another workstation.

    There are only tables. there are no linked tables.

    I have the following situation


    access <- ADODB-Connection over ODBC provider <- Postgresql

    I am reading the data from postgresql
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: source

    Originally posted by eperich
    The data source is a postgresql database on another workstation.

    There are only tables. there are no linked tables.

    I have the following situation


    access <- ADODB-Connection over ODBC provider <- Postgresql

    I am reading the data from postgresql
    ok I hope this helps, this is an example of a recordset that I use. The purpose of this recordset is to get a count from a table. What I did after I populated the recordset is then used an update query using the result from the recordset to update a table

    Dim Cur_DB As DAO.Database
    Dim Record_Set As DAO.Recordset
    Dim Where_Clause As String

    Set Cur_DB = CurrentDb()
    Set Record_Set = Cur_DB.OpenRecordset("select count(*) as count from [Table_PCRKMS_Local_Data]", dbOpenDynaset)

    DoCmd.RunSQL "INSERT INTO dbo_DM_LINE_PCRKMS_USAGE_LOG_TBL " _
    & "( User_ID,User_Phone, User_E_Mail, Version, Start_DateTime, Stop_DateTime,Rows_Returned, Where_Condition, access_version, department ) SELECT [Table:_PCRKMS_User_Defaults].User_Id,[Table:_PCRKMS_User_Defaults].User_Phone_Number,[Table:_PCRKMS_User_Defaults].User_MCS_Address, [Table:_PCRKMS_User_Defaults].Version_Number, #" & starttime & "#, #" & stoptime & "#, " & Record_Set("count") & ", " & Quote & Where_Clause & Quote & " ,'Access_2000','Global_Sales_Department' FROM [Table:_PCRKMS_User_Defaults];"


    Notice how i used it with the syntax & Record_Set("count") &
    I treated it like it was a variable naming the recordset and then the column from the recordset that I wanted. I also had to embed quotes around it, which i did by createing a variable called quote and set that variable to character = " Ill have to look that up if you need it.
    This is how I used the columns from the recordset, there may be an easier way though seeing how you have many columns.

    Lets see if someone else has an Idea
    Jim

  7. #7
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Lightbulb

    I'm little bit lost.... but

    this opens a recordset and prints all fieldnames and all values.... but I don't get the poing of this.... why don't you create table from ALL the fields: SELECT * INTO output FROM tblSample; tblSample is linked ODBC table - it's very fast and you can do it with VBA in 2 seconds.... with code bellow you have to figure out the data type for each field.... ODBC has it's standards and converts everything without problems (usually).

    jiri



    Sub ReadMyRecordSet()
    Dim myRecset As ADODB.Recordset
    Dim myConnection As String

    'change this connection string
    myConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\winnt\desktop\db1.mdb;" & _
    "User Id=admin;" & _
    "Password="


    Set myRecset = New ADODB.Recordset
    myRecset.Open "SELECT * FROM tblSample", myConnection, adOpenStatic, adLockReadOnly

    'This Lists ALL FIELD
    For Each myField In myRecset.Fields
    Debug.Print myField.Name
    Next

    'This Lists ALL Values
    Do While Not myRecset.EOF
    For i = 0 To myRecset.Fields.Count - 1
    Debug.Print myRecset.Fields(i).Name & ":" & myRecset.Fields(i).Value
    Next
    myRecset.MoveNext
    Loop

    myRecset.Close
    End Sub

Posting Permissions

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