Solved

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

Posted on 2011-09-07
27
348 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

770 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