Access 2007 ODBC Select column given a portion of the name of the column

Hi

I guess the SELECT statement you have is from the VBA code? Could you post the result of this, ie the actual select statement that's getting passed to the passthru query? All I can say looking at the above is that you may have too many quotes, and no spaces - so I'm guessing but this may be the sort of thing you want:

sSQL="SELECT AcctgModule, CurrentPeriod, Periods" & CurrentPeriod & "FrmDate, Periods" & CurrentPeriod & "ToDate  where AcctgModule=4"

Select allOpen in new window

That might help, otherwise please post more details.

Actually, I was using the SQL view of the Access query interface in hopes of a quick way to get the information.  I am gathering that I need to set this up in a VBA module in order to make it work.  What would the full code need to be?

What will you be using the result of the query for? I can write a function to show you how to return the data but it may need more code depending on what it's going to be used for. For example we could write a function that takes the period and writes the resulting data to a table, using ADO.  Are you using linked tables via ODBC?

The information is valuable at a number of levels, but as an example, I need to determine the last day associated with the accounting period (33 in this case).  To get it, I can only get to the pivotal GENERAL_GL table via a passthru statement via ODBC (for whatever reason, it cannot be linked through the usual Access link manager).

See if the attached file clarifies anymore.
Information-for-select-dates-bas.doc

Hi again,.

well there are lots of different ways to do this, but I've knocked up a quick example in VBA that uses ADO to connect to the remote database. You will need to put this in a module, and under Tools-> references select "Microsoft ActiveX Data Object" - the latest version listed.

Bear in mind that I'm guessing about field types and there may need to be some conversion done on the date fields (if they are date fields) to get them into the correct format for the Access table.

Function GetPeriodData(iPeriod As Integer) As Boolean

    Dim conADO As ADODB.Connection
    Dim rsADO As ADODB.Recordset
    Dim sSQL As String
    Dim tbdNewTable As DAO.TableDef
    Dim fldNew As DAO.Field
    
    On Error GoTo GetPeriodData_Error
    
    ' Delete table, if it exists:
    If DCount("*", "msysobjects", "Type = 1 AND name='NewTable'") > 0 Then

        DoCmd.DeleteObject acTable, "NewTable"
    
    End If
    
    ' Create table to hold the data:
    Set tbdNewTable = CurrentDb.CreateTableDef("NewTable")
    
    Set fldNew = tbdNewTable.CreateField("AcctgModule", DB_TEXT, 50)
    tbdNewTable.Fields.Append fldNew
    Set fldNew = tbdNewTable.CreateField("CurrentPeriod", DB_INTEGER)
    tbdNewTable.Fields.Append fldNew
    Set fldNew = tbdNewTable.CreateField("PeriodStart", DB_DATE)
    tbdNewTable.Fields.Append fldNew
    Set fldNew = tbdNewTable.CreateField("PeriodEnd", DB_DATE)
    tbdNewTable.Fields.Append fldNew
    
    CurrentDb.TableDefs.Append tbdNewTable
    
    ' Open an ADO connection to the ODBC data:
    
    Set conADO = New ADODB.Connection
    
    conADO.Open "your connection string in here"
    
    Set rsADO = New ADODB.Recordset
    
    sSQL = "SELECT AcctgModule, CurrentPeriod, Periods" & iPeriod & "FrmDate, Periods" & iPeriod & "ToDate  where AcctgModule=4"
    
    rsADO.Open sSQL, conADO, adOpenForwardOnly
    
    Do While Not rsADO.EOF
        sSQL = "insert into NewTable(AcctgModule, CurrentPeriod, PeriodStart,PeriodEnd) values ('" & rsADO("AcctgModule") & "'," & rsADO("CurrentPeriod") & "," & rsADO("Periods" & iPeriod & "FrmDate") & "," & rsADO("Periods" & iPeriod & "ToDate")
        CurrentDb.Execute sSQL
        rsADO.MoveNext
    Loop
    
    rsADO.Close
    conADO.Close
    
    Set rsADO = Nothing
    Set conADO = Nothing
    
    GetPeriodData = True
    Exit Function
    
    
GetPeriodData_Error:
    
    MsgBox "An error occurred: " & Err.Description & " (" & Err.Number & ")"
    GetPeriodData = False
    
End Function

Select allOpen in new window

I hope this is useful.

Looks very promising.  Unfortunately, I need to turn my attention elsewhere for the afternoon, but I will get back with you tomorrow.

Many thanks,
Tom

Hmmm....I seem to need help on the syntax of the connection string.  

Here is the string copied from the Access properties window for the passthru query

ODBC;DSN=BSI Peachtree;ServerName=SERVER2.1583;ServerDSN=BSI Peachtree;UID=Peachtree;PWD=letmein2;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP:SPX;ClientVersion=10.20.020.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;AutoD

Remove the leading "ODBC;"  - I meant to write that, there will be more issues, I expect, with the rest of the code depending on column types, etc....

Nothing is ever simple :-).  It is now down to a syntax error in the sql statement.  see attached.
syntax-error.doc

That's interesting, I've never seen that <<???>> before. It must be Pervasive specific. Can you post me the code line(s) that build the SQL string, just in case? Otherwise it will be worth changing the SQL to something you know is OK and running it just to make sure...

Regards
John

Ok - the syntax of the initial concatenated string did not includ "from GENERAL_GL"

Changed to

sSQL = "SELECT AcctgModule, CurrentPeriod, Periods" & iPeriod & "FrmDate, Periods" & iPeriod & "ToDate from GENERAL_GL where AcctgModule=4"

And it makes it past here BUT  does not execute
    Do While Not rsADO.EOF
       sSQL = "insert into NewTable(AcctgModule, CurrentPeriod, PeriodStart,PeriodEnd) values ('" & rsADO("AcctgModule") & "'," & rsADO("CurrentPeriod") & "," & rsADO("Periods" & iPeriod & "FrmDate") & "," & rsADO("Periods" & iPeriod & "ToDate")
        CurrentDb.Execute sSQL
        r'sADO.MoveNext

See attached message
syntax-error.doc

Whoops, I already fixed the r'sADO.MoveNext in the code to rsADO.MoveNext

Hi, that's probably because the PeriodStart and PeriodEnd fields are expecting a date and they're not getting one. At this point you should be able to inspect the contents of the columns rsADO("Periods" & iPeriod & "FrmDate")  and rsADO("Periods" & iPeriod & "ToDate") in the immediate window by typing

print rsADO("Periods" & iPeriod & "FrmDate")

Let's see what that looks like. Also, I just noticed there is a problem at the end of the SQL string which should have a closing ")" - so it should look like

 sSQL = "insert into NewTable(AcctgModule, CurrentPeriod, PeriodStart,PeriodEnd) values ('" & rsADO("AcctgModule") & "'," & rsADO("CurrentPeriod") & "," & rsADO("Periods" & iPeriod & "FrmDate") & "," & rsADO("Periods" & iPeriod & "ToDate") & ")"

Select allOpen in new window

You can also, of course, inspect the SQL string by doing a "print sSQL" in the immediate window.

However, I think we're getting there.

John

It did create the NewTable and populated it as follows:
AcctgModule        CurrentPeriod     PeriodStart         PeriodEnd
4                            33                         12/30/1899         12/30/1899

I'm guessing that your period isn't really end of December 1899?

Can you stop the code when it's at the line

CurrentDb.Execute sSQL

and print the contents of sSQL? To stop code click on the line, and press F9 which will highlight it (in a sort of brown colour), then when you run the program will stop at that point, and you can print sSQL in the immediate pane. Then paste it in here?

insert into NewTable(AcctgModule, CurrentPeriod, PeriodStart,PeriodEnd) values ('4',33,8/16/2011,9/13/2011)

Which are the right values

yup, nearly there - just add some # characters around the dates:

sSQL = "insert into NewTable(AcctgModule, CurrentPeriod, PeriodStart,PeriodEnd) values ('" & rsADO("AcctgModule") & "'," & rsADO("CurrentPeriod") & ",#" & rsADO("Periods" & iPeriod & "FrmDate") & "#,#" & rsADO("Periods" & iPeriod & "ToDate") & "#)"

Select allOpen in new window

YES!  the table is created correctly.  Now the only problem is if I try to rerun the function WITHOUT physically deleting NewTable, I get

"An error occurred: You can't carry out this action at the present time. (2486)"

Perhaps the function created a tear in the time continuum:-0

Yes, I fear you may be right. However, at the risk of upsetting the cosmic balance, have you by any chance got Newtable open to view the contents? If not then I'mnot sure what's up; maybe put a stop on the first line and step through (with the F8 key) to check each line until you hit the one it fails on, which I guess will be the line

DoCmd.DeleteObject acTable, "NewTable"

Also, if you want to copy the finished function back I'll double check it just in case...

1. In answer to viewing NewTable, yes, I have viewed it without problem and the data is correct

2. It does error out at DoCmd.DeleteObject acTable, "NewTable"

Option Compare Database

 
Function GetPeriodData(iPeriod As Integer) As Boolean

    Dim conADO As ADODB.Connection
    Dim rsADO As ADODB.Recordset
    Dim sSQL As String
    Dim tbdNewTable As DAO.TableDef
    Dim fldNew As DAO.Field
   
    On Error GoTo GetPeriodData_Error
   
    ' Delete table, if it exists:
    If DCount("*", "msysobjects", "Type = 1 AND name='NewTable'") > 0 Then

        DoCmd.DeleteObject acTable, "NewTable"
   
    End If
   
    ' Create table to hold the data:
    Set tbdNewTable = CurrentDb.CreateTableDef("NewTable")
   
    Set fldNew = tbdNewTable.CreateField("AcctgModule", DB_TEXT, 50)
    tbdNewTable.Fields.Append fldNew
    Set fldNew = tbdNewTable.CreateField("CurrentPeriod", DB_INTEGER)
    tbdNewTable.Fields.Append fldNew
    Set fldNew = tbdNewTable.CreateField("PeriodStart", DB_DATE)
    tbdNewTable.Fields.Append fldNew
    Set fldNew = tbdNewTable.CreateField("PeriodEnd", DB_DATE)
    tbdNewTable.Fields.Append fldNew
   
    CurrentDb.TableDefs.Append tbdNewTable
   
    ' Open an ADO connection to the ODBC data:
   
    Set conADO = New ADODB.Connection
   
    conADO.Open "DSN=BSI Peachtree;ServerName=SERVER2.1583;ServerDSN=BSI Peachtree;UID=Peachtree;PWD=letmein2;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP:SPX;ClientVersion=10.20.020.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;AutoDoubleQuote=0;;TABLE=GENERAL_GL"
   
    Set rsADO = New ADODB.Recordset
   
    sSQL = "SELECT AcctgModule, CurrentPeriod, Periods" & iPeriod & "FrmDate, Periods" & iPeriod & "ToDate from GENERAL_GL where AcctgModule=4"
 
    rsADO.Open sSQL, conADO, adOpenForwardOnly
 
   
    Do While Not rsADO.EOF
 
   
      sSQL = "insert into NewTable(AcctgModule, CurrentPeriod, PeriodStart,PeriodEnd) values ('" & rsADO("AcctgModule") & "'," & rsADO("CurrentPeriod") & ",#" & rsADO("Periods" & iPeriod & "FrmDate") & "#,#" & rsADO("Periods" & iPeriod & "ToDate") & "#)"
        CurrentDb.Execute sSQL
       


        rsADO.MoveNext
    Loop
   
    rsADO.Close
    conADO.Close
   
    Set rsADO = Nothing
    Set conADO = Nothing
   
    GetPeriodData = True
    Exit Function
   
   
GetPeriodData_Error:
   
    MsgBox "An error occurred: " & Err.Description & " (" & Err.Number & ")"
   

       
    GetPeriodData = False
   
End Function

Well that all looks OK. What I meant about viewing the table contents was that if you have the table open (ie viewing contents or in design mode) then the function can't delete it. Otherwise, I am not sure what's going wrong. I'll have a think but can you confirm the table is closed?

Yes, the NewTable was not open

Using the new code if gives and error message that "NewTable already exists."

Yes, sorry the IF statement should be as it was before:

 If DCount("*", "msysobjects", "Type = 1 AND name='NewTable'") > 0 Then

it's getting late here, must be losing concentratio....huh?

Works!  Certainly appreciate your prompt attention to this - you have more than earned the points!

Regards,
Tom

Went MORE than simply out of the way to fix it.

Thanks Tom, I appreciate your comments. I hope it proves useful.

Best regards
John