UDF XLA and SQL queries
Newbie question. Apologies in advance if it's too simplistic or in the wrong
forum.
I want to add an UDF to Excel. This UDF queries a SQL database.
Say the function name is 'MyFunc';
I want to be able to have (in a cell) =MyFunc("ABC") return some data from
the SQL database (for example here, say the Product Description for product
"ABC").
The user needs to select the database the first time he uses the workbook.
The UDF is called often in the worksheets of a given workbook so I would
prefer to open the connection once at the beginning and close it once at the
end instead of open/close each time the function is 'called' from a cell.
(After many tries) Here's what I did (simplified):
1. Created a XLA with the function.
2. Created a XLT with Workbook_Open event, something like this:
If DocumentProperties("MyDatabase") = NUL then
prompt for DatabaseName
store DatabaseName in DocumentProperties("MyDatabase")
End If
Open an ADO connection ("MyConn") to the Database specified in
DocumentProperties("MyDatabase")
End Sub
... and a Workbook_BeforeClose event:
MyConn.Close
Set MyConn = Nothing
End Sub
3. Things work fine: when I create a new workbook from the template it
prompts for the datatase and stores it in the workbook's properties... then
it creates and opens the connection fine ... so far so good.
4. Now the problem I have: In the XLA, when the function references the
connection ("MyConn") I get a compile error. Apparently because MyConn is
defined in the XLT and not in the XLA. I tried to set MyConn 'Public' but it
does not fix the issue.
My Question: Am I complicating things? Is there a better 'pattern' to use
here? Which one? Where is it documented? It seems to me this is all very
complicated for a simple task that someone else, surely, had to face before I
did!?!
I searched everywhere I could find before posting here. Any help/advice
would be appreciated.
Thanks
|
0 |
|
Utf
4/22/2010 7:14:01 PM
excel.programming
6508 articles. 
1 followers.
1 Replies
512 Views
This is how I tackle situations like these:
1. The same way as you I create the XLA & XLT combinnation
2. But different to you I create the Connection in the xla as oppose
to xlt. So now both your function and your connection object are in
the same VBA project and there are no calling conflicts.
Further to this - I don't think it's a good idea to keep the
connection open all the time. What I do instead is I store a public
connection variable in one of the xla modules and it gets created only
one time but it gets closed (but not destroyed) once I don't need to
pull any data anymore. So I've got a function that checks if the
public connection variable is set (is not nothing), if it is set, then
i just open it up when needed. If it's not set (i.e., if it is
nothing) - i create the new one and open it up. Once done with pulling
the data I close it (as per above - I don't set it to nothing so that
next time i need it i could just re-open it up).
This way the connecting to the db is very fast but at the same time i
don't keep it open to risk corrupting the db nor beefing up the
traffic.
3. For the xla to establish connection to the db that you need (i.e.,
the db name stored in your xlt document properties (or .xls for that
matter)) you can have a function in xlt that calls (and passes the db
name as argument) a function from xla that in turn stores the
connection variable.
Remember - now you've got both the eventual function and the
connection in the same xla project so the only thing you need is your
xla to know which db to connect to and you do that by calling one
function (xla) with another (xlt) and passing the db name as
parameter.
For you to have xlt able to call xla functions you set a reference to
the xla project from your xlt project (VBA Tolls>References>pick your
xla project here). And then you'll be able to call something like:
xlt:
Function ThisIsMyXLT_ProdDescripFunc() as string
ThisIsMyXLT_ProdDescripFunc =
ThisIsMyXLAProject.ThisIsMyXLAFunc(MydbVar)
End function
'For the xla you could write something like this (this is just a quick
and dirty snippet - it won't work and there might be some flaws in it
but you should get the idea)
xla:
Public ConnToDB as Object 'adjust as appropriate - i use late bind
Function ThisIsMyXLAFunc(MydbVar) as string
dim cn as object
If Not ConnToDB Is Nothing Then 'The variable already once
defined in this _
session - will reuse it
On Error GoTo CreateNewConnection:
set cn = ConnToDB
cn.Open
On Error GoTo 0
Else
'code here to create the new connection and store the
public ConnToDB variable for future reuse
End If
'''''''''''' here goes the code to set the ThisIsMyXLAFunc to
the necessary product description string using the cn connection
end function
Perhaps some of the above won't make sense straight away but it's
getting late here in Europe and I'm a bit tired...
|
|
0 |
|
AB
4/22/2010 8:38:10 PM
Similar Artilces:
Access: exclude data from a query based on two criteria
Re: a query in Access. I want to exclude two descriptions in a field, i.e., I have a Division field and I want to exclude "void" and "voidbus". Help! On Wed, 27 Jun 2007 08:36:00 -0700, JoanS wrote: > Re: a query in Access. I want to exclude two descriptions in a field, i.e., > I have a Division field and I want to exclude "void" and "voidbus". Help! If the entire field consists of just one word, then as criteria on that column, write: Not In("Void","Voidbus") -- Fred Please respond only to this newsgroup. I do not r...
Upgrading my Dynamics SQL 2000 install to SQL 2005
Can someone direct me to instructions on how to Upgrade my Dynamics SQL 2000 install to SQL 2005 It's reasonably simple. Back-up all databases, including Dynamics. Insert SQL 2005 DVD and use the upgrade feature. This will upgrade everything. Afterwards, login to GP and test. If all o.k., disgard back-ups. If it's no good, restore back-ups. "tstrop" wrote: > Can someone direct me to instructions on how to Upgrade my Dynamics SQL 2000 > install to SQL 2005 While in theory that should work, you may want to download the SQL 2005 upgrade advisor utility and run ...
SQL Server 2005 SP2 Cumulative Update 3 3186
Hello All, I'm about to apply SQL Server 2005 SP2 Cumulative Update 3 (build 3186) in our test environment. I'm curious to know if anyone has run into any issues with it and Dynamics GP 10. Thanks! Matt I should point out I'm doing this because this is the earliest sql 2005 build supported by PerformancePoint server (which I'm also about to install in test.) Thanks! "maatthias" wrote: > Hello All, > > I'm about to apply SQL Server 2005 SP2 Cumulative Update 3 (build 3186) in > our test environment. I'm curious to know if anyone has ru...
Data Query on the Fly
Does anybody have a Data Query on the fly example? I would like to query a Microsoft Access database and stuff the results into a new table. Can anybody help? I have used the External Data Function but would like to customize the SQL with data from the XLS sheet to return different results. Thank you in Advance, Mike Not exactly sure what you want to do, but perhaps http://www.erlandsendata.no/english/index.php?t=envbadac http://support.microsoft.com/default.aspx?scid=kb;en-us;257819 How To Use ADO with Excel Data from Visual Basic or VBA will be of some help. -- Regards, Tom ...
SQL UpdateTrigger allowed?
Hi, I did some customizing till now onto our CRM 3.0 and also added some livemaps within an iFrame as it could be found in some examples araound the web. Everthing works quite fine (as good as livemaps can find our european cities |-)) But the bad things is (by design of the adapted example) that if livemaps finds anything it stores the values in longitude / letitude values in the table. ok. Since than it always picks up those values for display without checking if the adress stored in CRM fits to it. Thats generally great, because its much faster than always search for the adress. Now...
Where Do I Learn SQL? Certification?
RMS V 1.3 - Now that we live and die by SQL, I am going to need to find some training. I have what I believe to be a pretty decent grasp of RMS, save but writing the custom reports myself, although I am wicked bad with a cut and paste from alternate reports. I know some of the fundamentals of SQL, but, I would like to know more. I am hoping for just shy of everything. Any thoughts? I am in Minneapolis, and think I would prefer classroom training over online. Thanks. jocelynp@kuhlmancompany.com Something to read while we're waiting for this gloomy weather to break.. Not looking forw...
Xpath / XPathExpression equivalent of SQL's "Top" function
Is there one? Also in either xpath or XPathExpression whats the best way to get the full xpath of a node. As i am using xpath query of (//books/.......) So I would like to see what is before the /book tag? > Is there one? Yes you can do /books/book[position()<6] That will select the first 5 book elements in the books node. > As i am using xpath query of (//books/.......) > > So I would like to see what is before the /book tag? Hmm you can get the parent node once you get the child I guess. -- Victor Hadianto http://www.synop.com/Products/SauceReader/ "ree32"...
UDF
Hi All, When entering a standard Excel function, it is showing all parameters as a short help on the function. Is this something we can do also with UDF and how? With kind regards, JP Last year I made some modifications to Fabrice Rimlinger's open source sparklines tool for a project I was working on, and added some help text. It has been too long for me to remember all the details, but the snippet below may lead you in the right direction. HTH, Keith Sub PiechartCreate3() 'PieChartCreate3 Dim StrDescription As String 'limit of approx 2...
Manupilate Query Results
Hi all. I need to change the way data is diplayed after a SQL query. What I am tying to do is organise data in such a way that MS project will open the file with minimum mapping. See below what I have got and what I need: I have got: Cust|CallType|Priority|CallID|Asignee|Date ect ect A | HR | X | 12 | Me | 0/0/2003 A | HR | X | 12 | John | 0/0/2003 B | Deve | Y | 14 | Stan | 0/1/2003 What I need the data to look like is: (the 1-4 is for MS Project Indents) 1 |A | 2 |HR| 3 |X | 4 |12 | Me | 0/0/2003 4 |12 | Joh...
Saving a UDF
I am perusing tutorials describing how to create a Custom Function. They describe how to create a module and enter the text for the function. But no mention is made of how or when it is saved. Also assuming that it is saved somehow in my default workbook, how do I ensure that it will be available with other already created workbooks? Thanks Brian Tozer Brian, if you want a UDF for one workbook only, just put it in the workbook where you are using it. If you want access to it always, put it in an empty workbook and save the workbook as an add-in *.xla. I believe Excel puts it in the corre...
run a macro via a query
I have a series of queries that are tied together in a macro. Example: The macro first deletes the data in an existing maketable. It then appends new data from a linked table, changing the formatting from Number to text. It then runs a final query that pulls data based on the appended table. I would like to have one query that a person could run that would run the macro. Is this possible? Thanks, Perry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1 Macros can run queries. Queries (as far as I know) do not run Macros --...
Query results inconsistent between computers
I have a situation where a few users are experiencing either a lockup or “Report Cancelled” when trying to run routine reports that have worked for them in the past. At first it appeared that perhaps there was a problem with printer drivers since the symptoms were the same as a diagnosed problem with that as a cause. The situation is this: I promoted a new level of MyApp.mde (level 6.07) in February. A few users reported having these problems, so I had them send me their MyApp_be.mdb tables; I could not reproduce the problem on my machines with either Access 2002 or 2003. I sent them stand...
Need Microsoft web hosting for Exchange, ASP, Sharepoint, and SQL
I currently have [URL=http://www.1and1.com/?k_id=7001514]1and1[/URL] as a web host for some clients because they have Windows servers with Exchange, ASP, Sharepoint, and SQL at really good rates. What I don't like is that they only pay a finders fee and what I want is the same services and products but with shared or reseller accounts rather than a finders fee. Anybody have any suggestion? Thanx, Dave. ...
SQL 2000 Enterprise AWE
Hi there I am having some problem on the MS SQL 2000 Ent x32 on window 2003 ent.I try to get the hotfix from Microsoft which is from the KB number 899761.Since this is to change the built number to 2040 and this patch is no longer valid, and it did have a newer hotfix.While I try to download the new hotfix but it seem the language support was just in ENGLISH and Japan.I am running on the type CHT, Chinese Edition SQL.Any advise will be appreciate. ...
Username from SQL-server..?
Hi all, I have Access form linked to a SQL-table. The user is logged in to a non-trusted SQL-server database via ODBC. How can I retrive the SQL-username in Access? I need the username as parameter to filter the records. Kent J. Code below. Replace "some_table" with the name of one of your linked tables. Dim qry As DAO.QueryDef Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set qry = db.CreateQueryDef("") qry.Connect = db.TableDefs("some_table").Connect qry.SQL = "SELECT current_user AS username" qry.ReturnsRecords = True Set r...
Without Matching Query Limit ?
Hi All, The Without Matching query wizard works fine until you select more than 13 columns to compare. My guess is that the 13 " AND " 's in the SQL left join statement are the problem. Unfortunely, my tables have 16 fields to compare. Any sugestions ? Stuart wrote: >The Without Matching query wizard works fine until you select more than 13 >columns to compare. My guess is that the 13 " AND " 's in the SQL left join >statement are the problem. >Unfortunely, my tables have 16 fields to compare. Don't use the wizard ;-) Seriously, after you d...
SQL Server 200 Licensing
Hello: A client of ours is on SQL Server 2000. They are going to buy a new server and place their data on their "soon to be old" SQL Server 2000 box on this new server. Do they have to buy a brand new licensed copy of SQL Server 2000 for this new server, or can they just use the same SQL Server 2000 CD and load SQL Server 2000 onto their new server that way? I just want to make sure that there is no licensing violation if they use the same SQL 2000 CD on a separate box. Again, they will not be using their old SQL box after they make the conversion. Thanks! childofthe1980...
Forms to update queries
I have created multiple tables: cust_tbl - list of customers(will not change) prod_tbl - list of products (will not change) bud_tbl - commitment level at the beginning of the year (will not change) mon.spend_tbl - customer's monthly spend (will vary monthly) I have a query that will combine the tables to provide a list of customers, the customer's related prodcuts, the customer's commitment, the monthly spend and the variance between the commitment and monthly spend. How can I set up a form that will update the criteria in the query based off a list of customers and produ...
sql sp4 to server
If I apply sql 2000 sp4 do I also need to apply sp4 to the desktop engine on my laptop clients using outlook client? I meant to say that If I apply sql sp4 to the CRM server (which is running sql locally) will I also need to aplly sp4 to the MSDE instances on my laptop users PC's using the Outlook client? Sorry just a little anxious\nervous when it comes to CRM 1.2. Trying to get to CRM 3.0 "dstinnett" wrote: > If I apply sql 2000 sp4 do I also need to apply sp4 to the desktop engine on > my laptop clients using outlook client? Yes sp4 is required for the msde. ...
Addin UDF Registration/Declaration
Any guidance as to how to have a UDF in an addin be declared/registered etc. so as to be used like all other usual/normal functions? like be autocompleted upon partial entry, arguments be displayed in the supertip and be capable to be used in the Conditional Formatting and other features? -- Thanx in advance & Best Regards, Faraz! Read this: http://www.jkp-ads.com/articles/RegisterUDF00.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "Faraz Ahmed Qureshi" <FarazAhmedQureshi@discussions.microsoft.com> wrote in message news:B5447321-57FD...
Basing a report on an updated cross-tab query
I have a cross-tab query based on a table that I need to update daily. (The main fields are totals by date.) I have a report based on that query that I need to run daily following the table update. I get an error message as soon as a new date appears in the table and query. The report seems to keep the dates hard-coded. What can I do to remedy this situation? Lotharia - The only thing I know is you can do a SELECT * in your second query. If you list fieldnames, then as the crosstab changes, you have to manually change the second query. -- Daryl S "Lothar...
Access to SQL Server (?) conversion?
I currently have a single user Access database with many queries, forms, reports, VBA, etc.. I need to expand on it to allow multiple user access, with 2-3 with read/write and maybe 6 read only. Not knowing much on SQL Server, what would be involved in porting the server side and the client side? (I'm hoping I can keep most of the current access stuff). Would it be basically the same amount of work if I went to something like MySQL? TIA Why? As in "why would you need to migrate to SQL-Server?" Access would be quite capable of handling that many simultaneous users. You cou...
SQL Update statement ask for input
Hi, I use variable in SQL statement, but it says "Enter Paramenter" instead. Here is my statement: Enbr = CStr(Month(CurM)) If Enbr <= "9" Then Enbr = "0" + Enbr End If Enbr = CStr(Year(CurM)) + "-" + Enbr DoCmd.RunSQL "Update dailyInvc_tb3 Set LogMonth = Enbr" Any idea? Thank you. GL Enbr is a string, so you have to construct the sql statement differently.Try this: Update dailyInvc_tb3 Set LogMonth = " & char(39) & Enbr & chr(39). This puts single quotes around the string value and adds it to sql s...
ANN: New XML Videos Demonstrating Data Integration and Web Publishing using Microsoft SQL Server 2000, SQL/XML, XSLT, and Stylus Studio
[announce]Dear Microsoft .NET XML enthusiasts, We recently published two new XML video demonstrations which illustrate how to create SQL/XML queries to access relational data and render it as XML using Microsoft SQL Server 2000 and Stylus Studio DB-to-XML Data Sources. The new videos are freely available from the Stylus Studio website at: http://www.stylusstudio.com/learn_sql_xml.html The Working with Relational Data as XML series of videos shows two methods for creating these powerful data sources, and how they can be reused in Stylus Studio anywhere you might use XML, such as in the creati...
Missing 512 records in a query
I was setting up a query that had been troublesome so I was pulling each element of the query separately to locate which of 5 related tables was causing the problem. I forgot to enter a criteria in the first element of the query so it should have retrieved the entire table. The table contains 24100 records but the query only retrieved 23588. A little investigation found a few of the missing records and I can find them in the table but they don't appear in the query. Why don't they appear in the query? What have I done wrong that a query can't see these records they look fin...
Re: a query in Access. I want to exclude two descriptions in a field, i.e., I have a Division field and I want to exclude "void" and "voidbus". Help! On Wed, 27 Jun 2007 08:36:00 -0700, JoanS wrote: > Re: a query in Access. I want to exclude two descriptions in a field, i.e., > I have a Division field and I want to exclude "void" and "voidbus". Help! If the entire field consists of just one word, then as criteria on that column, write: Not In("Void","Voidbus") -- Fred Please respond only to this newsgroup. I do not r...
Upgrading my Dynamics SQL 2000 install to SQL 2005
Can someone direct me to instructions on how to Upgrade my Dynamics SQL 2000 install to SQL 2005 It's reasonably simple. Back-up all databases, including Dynamics. Insert SQL 2005 DVD and use the upgrade feature. This will upgrade everything. Afterwards, login to GP and test. If all o.k., disgard back-ups. If it's no good, restore back-ups. "tstrop" wrote: > Can someone direct me to instructions on how to Upgrade my Dynamics SQL 2000 > install to SQL 2005 While in theory that should work, you may want to download the SQL 2005 upgrade advisor utility and run ...
SQL Server 2005 SP2 Cumulative Update 3 3186
Hello All, I'm about to apply SQL Server 2005 SP2 Cumulative Update 3 (build 3186) in our test environment. I'm curious to know if anyone has run into any issues with it and Dynamics GP 10. Thanks! Matt I should point out I'm doing this because this is the earliest sql 2005 build supported by PerformancePoint server (which I'm also about to install in test.) Thanks! "maatthias" wrote: > Hello All, > > I'm about to apply SQL Server 2005 SP2 Cumulative Update 3 (build 3186) in > our test environment. I'm curious to know if anyone has ru...
Data Query on the Fly
Does anybody have a Data Query on the fly example? I would like to query a Microsoft Access database and stuff the results into a new table. Can anybody help? I have used the External Data Function but would like to customize the SQL with data from the XLS sheet to return different results. Thank you in Advance, Mike Not exactly sure what you want to do, but perhaps http://www.erlandsendata.no/english/index.php?t=envbadac http://support.microsoft.com/default.aspx?scid=kb;en-us;257819 How To Use ADO with Excel Data from Visual Basic or VBA will be of some help. -- Regards, Tom ...
SQL UpdateTrigger allowed?
Hi, I did some customizing till now onto our CRM 3.0 and also added some livemaps within an iFrame as it could be found in some examples araound the web. Everthing works quite fine (as good as livemaps can find our european cities |-)) But the bad things is (by design of the adapted example) that if livemaps finds anything it stores the values in longitude / letitude values in the table. ok. Since than it always picks up those values for display without checking if the adress stored in CRM fits to it. Thats generally great, because its much faster than always search for the adress. Now...
Where Do I Learn SQL? Certification?
RMS V 1.3 - Now that we live and die by SQL, I am going to need to find some training. I have what I believe to be a pretty decent grasp of RMS, save but writing the custom reports myself, although I am wicked bad with a cut and paste from alternate reports. I know some of the fundamentals of SQL, but, I would like to know more. I am hoping for just shy of everything. Any thoughts? I am in Minneapolis, and think I would prefer classroom training over online. Thanks. jocelynp@kuhlmancompany.com Something to read while we're waiting for this gloomy weather to break.. Not looking forw...
Xpath / XPathExpression equivalent of SQL's "Top" function
Is there one? Also in either xpath or XPathExpression whats the best way to get the full xpath of a node. As i am using xpath query of (//books/.......) So I would like to see what is before the /book tag? > Is there one? Yes you can do /books/book[position()<6] That will select the first 5 book elements in the books node. > As i am using xpath query of (//books/.......) > > So I would like to see what is before the /book tag? Hmm you can get the parent node once you get the child I guess. -- Victor Hadianto http://www.synop.com/Products/SauceReader/ "ree32"...
UDF
Hi All, When entering a standard Excel function, it is showing all parameters as a short help on the function. Is this something we can do also with UDF and how? With kind regards, JP Last year I made some modifications to Fabrice Rimlinger's open source sparklines tool for a project I was working on, and added some help text. It has been too long for me to remember all the details, but the snippet below may lead you in the right direction. HTH, Keith Sub PiechartCreate3() 'PieChartCreate3 Dim StrDescription As String 'limit of approx 2...
Manupilate Query Results
Hi all. I need to change the way data is diplayed after a SQL query. What I am tying to do is organise data in such a way that MS project will open the file with minimum mapping. See below what I have got and what I need: I have got: Cust|CallType|Priority|CallID|Asignee|Date ect ect A | HR | X | 12 | Me | 0/0/2003 A | HR | X | 12 | John | 0/0/2003 B | Deve | Y | 14 | Stan | 0/1/2003 What I need the data to look like is: (the 1-4 is for MS Project Indents) 1 |A | 2 |HR| 3 |X | 4 |12 | Me | 0/0/2003 4 |12 | Joh...
Saving a UDF
I am perusing tutorials describing how to create a Custom Function. They describe how to create a module and enter the text for the function. But no mention is made of how or when it is saved. Also assuming that it is saved somehow in my default workbook, how do I ensure that it will be available with other already created workbooks? Thanks Brian Tozer Brian, if you want a UDF for one workbook only, just put it in the workbook where you are using it. If you want access to it always, put it in an empty workbook and save the workbook as an add-in *.xla. I believe Excel puts it in the corre...
run a macro via a query
I have a series of queries that are tied together in a macro. Example: The macro first deletes the data in an existing maketable. It then appends new data from a linked table, changing the formatting from Number to text. It then runs a final query that pulls data based on the appended table. I would like to have one query that a person could run that would run the macro. Is this possible? Thanks, Perry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1 Macros can run queries. Queries (as far as I know) do not run Macros --...
Query results inconsistent between computers
I have a situation where a few users are experiencing either a lockup or “Report Cancelled” when trying to run routine reports that have worked for them in the past. At first it appeared that perhaps there was a problem with printer drivers since the symptoms were the same as a diagnosed problem with that as a cause. The situation is this: I promoted a new level of MyApp.mde (level 6.07) in February. A few users reported having these problems, so I had them send me their MyApp_be.mdb tables; I could not reproduce the problem on my machines with either Access 2002 or 2003. I sent them stand...
Need Microsoft web hosting for Exchange, ASP, Sharepoint, and SQL
I currently have [URL=http://www.1and1.com/?k_id=7001514]1and1[/URL] as a web host for some clients because they have Windows servers with Exchange, ASP, Sharepoint, and SQL at really good rates. What I don't like is that they only pay a finders fee and what I want is the same services and products but with shared or reseller accounts rather than a finders fee. Anybody have any suggestion? Thanx, Dave. ...
SQL 2000 Enterprise AWE
Hi there I am having some problem on the MS SQL 2000 Ent x32 on window 2003 ent.I try to get the hotfix from Microsoft which is from the KB number 899761.Since this is to change the built number to 2040 and this patch is no longer valid, and it did have a newer hotfix.While I try to download the new hotfix but it seem the language support was just in ENGLISH and Japan.I am running on the type CHT, Chinese Edition SQL.Any advise will be appreciate. ...
Username from SQL-server..?
Hi all, I have Access form linked to a SQL-table. The user is logged in to a non-trusted SQL-server database via ODBC. How can I retrive the SQL-username in Access? I need the username as parameter to filter the records. Kent J. Code below. Replace "some_table" with the name of one of your linked tables. Dim qry As DAO.QueryDef Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set qry = db.CreateQueryDef("") qry.Connect = db.TableDefs("some_table").Connect qry.SQL = "SELECT current_user AS username" qry.ReturnsRecords = True Set r...
Without Matching Query Limit ?
Hi All, The Without Matching query wizard works fine until you select more than 13 columns to compare. My guess is that the 13 " AND " 's in the SQL left join statement are the problem. Unfortunely, my tables have 16 fields to compare. Any sugestions ? Stuart wrote: >The Without Matching query wizard works fine until you select more than 13 >columns to compare. My guess is that the 13 " AND " 's in the SQL left join >statement are the problem. >Unfortunely, my tables have 16 fields to compare. Don't use the wizard ;-) Seriously, after you d...
SQL Server 200 Licensing
Hello: A client of ours is on SQL Server 2000. They are going to buy a new server and place their data on their "soon to be old" SQL Server 2000 box on this new server. Do they have to buy a brand new licensed copy of SQL Server 2000 for this new server, or can they just use the same SQL Server 2000 CD and load SQL Server 2000 onto their new server that way? I just want to make sure that there is no licensing violation if they use the same SQL 2000 CD on a separate box. Again, they will not be using their old SQL box after they make the conversion. Thanks! childofthe1980...
Forms to update queries
I have created multiple tables: cust_tbl - list of customers(will not change) prod_tbl - list of products (will not change) bud_tbl - commitment level at the beginning of the year (will not change) mon.spend_tbl - customer's monthly spend (will vary monthly) I have a query that will combine the tables to provide a list of customers, the customer's related prodcuts, the customer's commitment, the monthly spend and the variance between the commitment and monthly spend. How can I set up a form that will update the criteria in the query based off a list of customers and produ...
sql sp4 to server
If I apply sql 2000 sp4 do I also need to apply sp4 to the desktop engine on my laptop clients using outlook client? I meant to say that If I apply sql sp4 to the CRM server (which is running sql locally) will I also need to aplly sp4 to the MSDE instances on my laptop users PC's using the Outlook client? Sorry just a little anxious\nervous when it comes to CRM 1.2. Trying to get to CRM 3.0 "dstinnett" wrote: > If I apply sql 2000 sp4 do I also need to apply sp4 to the desktop engine on > my laptop clients using outlook client? Yes sp4 is required for the msde. ...
Addin UDF Registration/Declaration
Any guidance as to how to have a UDF in an addin be declared/registered etc. so as to be used like all other usual/normal functions? like be autocompleted upon partial entry, arguments be displayed in the supertip and be capable to be used in the Conditional Formatting and other features? -- Thanx in advance & Best Regards, Faraz! Read this: http://www.jkp-ads.com/articles/RegisterUDF00.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "Faraz Ahmed Qureshi" <FarazAhmedQureshi@discussions.microsoft.com> wrote in message news:B5447321-57FD...
Basing a report on an updated cross-tab query
I have a cross-tab query based on a table that I need to update daily. (The main fields are totals by date.) I have a report based on that query that I need to run daily following the table update. I get an error message as soon as a new date appears in the table and query. The report seems to keep the dates hard-coded. What can I do to remedy this situation? Lotharia - The only thing I know is you can do a SELECT * in your second query. If you list fieldnames, then as the crosstab changes, you have to manually change the second query. -- Daryl S "Lothar...
Access to SQL Server (?) conversion?
I currently have a single user Access database with many queries, forms, reports, VBA, etc.. I need to expand on it to allow multiple user access, with 2-3 with read/write and maybe 6 read only. Not knowing much on SQL Server, what would be involved in porting the server side and the client side? (I'm hoping I can keep most of the current access stuff). Would it be basically the same amount of work if I went to something like MySQL? TIA Why? As in "why would you need to migrate to SQL-Server?" Access would be quite capable of handling that many simultaneous users. You cou...
SQL Update statement ask for input
Hi, I use variable in SQL statement, but it says "Enter Paramenter" instead. Here is my statement: Enbr = CStr(Month(CurM)) If Enbr <= "9" Then Enbr = "0" + Enbr End If Enbr = CStr(Year(CurM)) + "-" + Enbr DoCmd.RunSQL "Update dailyInvc_tb3 Set LogMonth = Enbr" Any idea? Thank you. GL Enbr is a string, so you have to construct the sql statement differently.Try this: Update dailyInvc_tb3 Set LogMonth = " & char(39) & Enbr & chr(39). This puts single quotes around the string value and adds it to sql s...
ANN: New XML Videos Demonstrating Data Integration and Web Publishing using Microsoft SQL Server 2000, SQL/XML, XSLT, and Stylus Studio
[announce]Dear Microsoft .NET XML enthusiasts, We recently published two new XML video demonstrations which illustrate how to create SQL/XML queries to access relational data and render it as XML using Microsoft SQL Server 2000 and Stylus Studio DB-to-XML Data Sources. The new videos are freely available from the Stylus Studio website at: http://www.stylusstudio.com/learn_sql_xml.html The Working with Relational Data as XML series of videos shows two methods for creating these powerful data sources, and how they can be reused in Stylus Studio anywhere you might use XML, such as in the creati...
Missing 512 records in a query
I was setting up a query that had been troublesome so I was pulling each element of the query separately to locate which of 5 related tables was causing the problem. I forgot to enter a criteria in the first element of the query so it should have retrieved the entire table. The table contains 24100 records but the query only retrieved 23588. A little investigation found a few of the missing records and I can find them in the table but they don't appear in the query. Why don't they appear in the query? What have I done wrong that a query can't see these records they look fin...