Solved

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

Posted on 2011-09-07
27
344 Views
Last Modified: 2012-05-12
I currently am doing a passthru query (QRY_GENERAL_GL_PASSTHRU_DATES) in Access 2007 to an ODBC table to return columns which indicate the current period reference (for example, 33) and all the related dates to all the current period for the start (Periods33FrmDate) and the end (Periods33ToDate).  The odd thing (this link is to Peachtree GL) is the array is all on one row with each of the column dates being a separate column.  For example, all the necessary data is on one row, with Peachtree already supplying 33 in the CurrentPeriod column.  The entire row is comprised of an array with column names of Periods0FrmDate to Periods40FrmDate (41 columns) and Periods0ToDate to Periods40ToDate.  I just want to return the columns with the "33" in the middle of the name.  The concatenation piece continues to blow up, but if hard coded as Periods33FrmDate works fine.
SELECT AcctgModule,CurrentPeriod, "Periods"&CurrentPeriod&"FrmDate", "Periods"&CurrentPeriod&"ToDate"  where AcctgModule=4
0
Comment
Question by:thelton_us
  • 14
  • 13
27 Comments
 
LVL 10

Expert Comment

by:plummet
ID: 36497135
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"

Open in new window


That might help, otherwise please post more details.

0
 

Author Comment

by:thelton_us
ID: 36497341
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?
0
 
LVL 10

Expert Comment

by:plummet
ID: 36497491
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?



0
 

Author Comment

by:thelton_us
ID: 36497772
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 36498331
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

Open in new window


I hope this is useful.
0
 

Author Comment

by:thelton_us
ID: 36499009
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
0
 

Author Comment

by:thelton_us
ID: 36502636
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 36502692
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....
0
 

Author Comment

by:thelton_us
ID: 36503178
Nothing is ever simple :-).  It is now down to a syntax error in the sql statement.  see attached.
syntax-error.doc
0
 
LVL 10

Expert Comment

by:plummet
ID: 36503357
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
0
 

Author Comment

by:thelton_us
ID: 36503628
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
0
 

Author Comment

by:thelton_us
ID: 36503640
Whoops, I already fixed the r'sADO.MoveNext in the code to rsADO.MoveNext
0
 
LVL 10

Expert Comment

by:plummet
ID: 36503794
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") & ")"

Open 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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:thelton_us
ID: 36504225
It did create the NewTable and populated it as follows:
AcctgModule        CurrentPeriod     PeriodStart         PeriodEnd
4                            33                         12/30/1899         12/30/1899
0
 
LVL 10

Expert Comment

by:plummet
ID: 36504376
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?

0
 

Author Comment

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

Which are the right values
0
 
LVL 10

Expert Comment

by:plummet
ID: 36504578
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") & "#)"

Open in new window

0
 

Author Comment

by:thelton_us
ID: 36504692
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 36504940
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...
0
 

Author Comment

by:thelton_us
ID: 36505124
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 36505204
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?
0
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 36505569
Also, I think we don't need to delete the table and recreate it, we could just delete the contents. That might get around the problem, too.

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

	' Empty table, if it exists:
	If DCount("*", "msysobjects", "Type = 1 AND name='NewTable'") = 0 Then
	
		sSQL="delete from NewTable"
		currentDB.Execute sSQL
	
	else
	
		' 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
	
	end if
	
(etc.......)

Open in new window

0
 

Author Comment

by:thelton_us
ID: 36505813
Yes, the NewTable was not open

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

Expert Comment

by:plummet
ID: 36505876
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?
0
 

Author Comment

by:thelton_us
ID: 36505934
Works!  Certainly appreciate your prompt attention to this - you have more than earned the points!

Regards,
Tom
0
 

Author Closing Comment

by:thelton_us
ID: 36505947
Went MORE than simply out of the way to fix it.
0
 
LVL 10

Expert Comment

by:plummet
ID: 36506016
Thanks Tom, I appreciate your comments. I hope it proves useful.

Best regards
John
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now