Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Display Modes
  #21  
Old 12-05-2017, 05:42 PM
Sleeping Dragon Sleeping Dragon is offline
Member
 
Join Date: Jun 2003
Posts: 123
Default

Check the SrcQry. Is that a pre-built query in the Access DB? When an Access query references an unknown field, Access will ask to enter a parameter value.
Reply With Quote
  #22  
Old 12-05-2017, 06:53 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by Sleeping Dragon View Post
Check the SrcQry. Is that a pre-built query in the Access DB? When an Access query references an unknown field, Access will ask to enter a parameter value.
Thanks for the comment, Sleeping Dragon.

Yes, I found an expression in one of the fields that referred to [Current Year], and I could not find that variable. Hence, the parameter input for [Current Year]. I think I need to fix the queries in the Access database before proceeding with the import.
Reply With Quote
  #23  
Old 12-05-2017, 10:45 PM
Arlie_Proctor Arlie_Proctor is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: N.J.
College: Indiana University
Favorite beer: Becks
Posts: 1,170
Default

If I am building SQL statements for use in ADO, I like to create a function to build the query. You can pass criteria as arguments to the function or take them from ranges/cells in Excel. One handy addition is to add a carriage return at the end of each line so that the SQL is readable and easier to debug when you dump it to the debug window. I don't know if Access is amenable to the additional carriage returns, but they are fine with both Oracle and SQL Server.

Function QryString(fncParameter as String) As String

Dim strSQL As String

' Carriage return added on each line to make SQL readable in debug window
strSQL = "SELECT " & chr(13)
strSQL = strSQL & " Field1, " & Chr(13)
strSQL = strSQL & " Field2 " & Chr(13)
strSQL = strSQL & "FROM " & chr(13)
strSQL = strSQL & " Table1 " & Chr(13)
strSQL = strSQL & "Where " & chr(13)
strSQL = strSQL & " Field1=" & Range("Criteria1").value & " and " & chr(13)
strSQL = strSQL & " Field2=" & fncParameter

QryString = strSQL
End Function

So, if you type ?qryString("XYZ") in the debug window, you get:

SELECT
Field1,
Field2
FROM Table1
Where
Field1='ABC' and
Field2='XYZ'

Last edited by Arlie_Proctor; 12-05-2017 at 10:47 PM.. Reason: Indentation
Reply With Quote
  #24  
Old 12-06-2017, 09:40 AM
SlowMotionWalter's Avatar
SlowMotionWalter SlowMotionWalter is offline
Member
CAS
 
Join Date: Jun 2013
Posts: 10,811
Default

Quote:
Originally Posted by Arlie_Proctor View Post
If I am building SQL statements for use in ADO, I like to create a function to build the query. You can pass criteria as arguments to the function or take them from ranges/cells in Excel. One handy addition is to add a carriage return at the end of each line so that the SQL is readable and easier to debug when you dump it to the debug window. I don't know if Access is amenable to the additional carriage returns, but they are fine with both Oracle and SQL Server.

Function QryString(fncParameter as String) As String

Dim strSQL As String

' Carriage return added on each line to make SQL readable in debug window
strSQL = "SELECT " & chr(13)
strSQL = strSQL & " Field1, " & Chr(13)
strSQL = strSQL & " Field2 " & Chr(13)
strSQL = strSQL & "FROM " & chr(13)
strSQL = strSQL & " Table1 " & Chr(13)
strSQL = strSQL & "Where " & chr(13)
strSQL = strSQL & " Field1=" & Range("Criteria1").value & " and " & chr(13)
strSQL = strSQL & " Field2=" & fncParameter

QryString = strSQL
End Function

So, if you type ?qryString("XYZ") in the debug window, you get:

SELECT
Field1,
Field2
FROM Table1
Where
Field1='ABC' and
Field2='XYZ'
Totally stealing this next time I need to use updatable SQL in XLVBA. So much more readable than the mess I've had in the past.
__________________
Quote:
Originally Posted by Kangaz wit Attitude View Post
Force of habit, 2pac's been typing "88" a LOT since Trump got elected.
Spoiler:
Quote:
Originally Posted by JMO View Post
You ought to see the bush. It's impressive.
Quote:
Originally Posted by Pikachu
10:53 pm: some ppl dont take advices well
Quote:
Originally Posted by mayo fan
9:45 pm: ao fan would be hot covered in mayo!
Quote:
Originally Posted by Snikelfritz
if you'd like I can come visit and dress up like a girl and get in some fights
Quote:
Originally Posted by Kaner3339 View Post
i think everyone needs to do this type of thing to get a dose of reality and straighten people up. it's kinda like going to the mountains and becoming a monk except it's with hundreds of potatoes and a lot of stoners with tattoos in a kitchen
Reply With Quote
  #25  
Old 12-06-2017, 11:26 AM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by Arlie_Proctor View Post
If I am building SQL statements for use in ADO, I like to create a function to build the query. You can pass criteria as arguments to the function or take them from ranges/cells in Excel.
Thanks for the helpful advice, Arlie, and for the code.

I think Access would be fine with the extra carriage returns (why do they still call them that? Do they even make typewriters any more? ).

Access uses SQL as its underlying language, although I don't know SQL. I always use design mode, not SQL mode, when I am working in Access.
Reply With Quote
  #26  
Old 12-06-2017, 10:18 PM
Arlie_Proctor Arlie_Proctor is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: N.J.
College: Indiana University
Favorite beer: Becks
Posts: 1,170
Default

Never assume Access is amenable to anything a real RDBMS can handle. I do teach it because everybody has it, but it's a quirky beast...and that's the nicest thing I can say about it. Fortunately, I rarely need to make Excel or anything else talk to Access via ADO, so I don't worry much about it. Concentrate on making Excel, R, SAS, SPSS, etc. talk to Oracle or SQL Server. That is time much better spent in terms of valuable learning.

Quote:
Originally Posted by Freebird View Post
Thanks for the helpful advice, Arlie, and for the code.

I think Access would be fine with the extra carriage returns (why do they still call them that? Do they even make typewriters any more? ).

Access uses SQL as its underlying language, although I don't know SQL. I always use design mode, not SQL mode, when I am working in Access.
Reply With Quote
  #27  
Old 12-07-2017, 11:48 AM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by Arlie_Proctor View Post
Never assume Access is amenable to anything a real RDBMS can handle. I do teach it because everybody has it, but it's a quirky beast...and that's the nicest thing I can say about it. Fortunately, I rarely need to make Excel or anything else talk to Access via ADO, so I don't worry much about it. Concentrate on making Excel, R, SAS, SPSS, etc. talk to Oracle or SQL Server. That is time much better spent in terms of valuable learning.
Thanks for the helpful advice, Arlie. I will keep that in mind.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 03:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.13861 seconds with 11 queries