Currently I am using the RegExp object to parse a large dataset in an Access
table - but this table was exported from SQL Server, and the very correct
question was asked - why not just do it in SQL Server.
What would be the best way to convert the VBA code I use in Access to SQL
Server - being only marginally familiar with T-SQL syntax and not at all
familiar with what can or cannot be done?
--
Darryl Kerkeslager 8 3324
On Fri, 8 Dec 2006 17:05:36 -0500, "Darryl Kerkeslager"
<ke*********@co mcast.netwrote:
RegEx is not inherently supported in T-SQL.
In the larger picture, parsing is a middle-tier activity, and does not
really belong in the data layer.
-Tom.
>Currently I am using the RegExp object to parse a large dataset in an Access table - but this table was exported from SQL Server, and the very correct question was asked - why not just do it in SQL Server.
What would be the best way to convert the VBA code I use in Access to SQL Server - being only marginally familiar with T-SQL syntax and not at all familiar with what can or cannot be done?
"Darryl Kerkeslager" <ke*********@co mcast.netwrote in
news:FP******** *************** *******@comcast .com:
Currently I am using the RegExp object to parse a large dataset in an
Access table - but this table was exported from SQL Server, and the
very correct question was asked - why not just do it in SQL Server.
If it's done on SQL Server and multiple users are using it, or any
function, procedure whatever on SQL Server, then RegExp will share CPU
cycles, memory and bus with the demands of all those users on one
machine, the server machine.
If it's done locally then RegExp uses the client machine's CPU which is
very likely to have many fewer demands than the server machine, and
often is idle while the server is struggling.
The notion that we should "do" things on the server by preference is
counter-productive. We should "do" things on the server when the server
has special capabilities the local machine does not, and, more often,
when those things will reduce significantly the amount of data that
flows across our connection with the server.
--
Lyle Fairfield http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm
(just a sad story - read if bored)
"Tom van Stiphout" <no************ *@cox.netwrote
RegEx is not inherently supported in T-SQL.
Is there any type of regular expression parsing that can be used?
In the larger picture, parsing is a middle-tier activity, and does not
really belong in the data layer.
Perhaps I should have framed my question differently. What I am trying to
accomplish is a one-time data cleanup, some 8 months hence. Heck, it may
take that long to perfect the code. I am testing the process by getting an
export of 49K distinct rows from SQL Server -Excel ->Access -Excel, just
so that the spreadsheet can serve as a lookup table for the conversion
process. My process will run a day before the conversion, to provide the
lookup of Distinct value; conversion will actually use the lookup table on
3M+ rows.
Obviously, this is a kluge which would be far better if the data lookup
table could just be created in SQL Server - but I'm not the SQL Server
person, I'm just writing the regular expressions. So the question is, how
would the SQL Server person run my VBA code (or convert it to another
runnable format, like T-SQL) so that all this exporting to Access could be
bypassed?
--
Darryl Kerkeslager
Lyle,
See my reply to Tom. I think I did not explain my issue clearly, and I have
attempted to do so again. Any help greatly appreciated.
--
Darryl Kerkeslager
On Sun, 10 Dec 2006 18:28:55 -0500, "Darryl Kerkeslager"
<ke*********@co mcast.netwrote:
Good luck putting 3M rows in Excel. It's still not clear to me what
you want.
Since this is a one-time shot, I would likely use Access to link to
the SQL Server table, and then run RegEx from VBA.
If you MUST run the procedure on SQL Server, you may need version
2005, and write a DotNet assembly which is callable from an sproc.
-Tom.
>"Tom van Stiphout" <no************ *@cox.netwrote
>RegEx is not inherently supported in T-SQL.
Is there any type of regular expression parsing that can be used?
>In the larger picture, parsing is a middle-tier activity, and does not really belong in the data layer.
Perhaps I should have framed my question differently. What I am trying to accomplish is a one-time data cleanup, some 8 months hence. Heck, it may take that long to perfect the code. I am testing the process by getting an export of 49K distinct rows from SQL Server -Excel ->Access -Excel, just so that the spreadsheet can serve as a lookup table for the conversion process. My process will run a day before the conversion, to provide the lookup of Distinct value; conversion will actually use the lookup table on 3M+ rows.
Obviously, this is a kluge which would be far better if the data lookup table could just be created in SQL Server - but I'm not the SQL Server person, I'm just writing the regular expressions. So the question is, how would the SQL Server person run my VBA code (or convert it to another runnable format, like T-SQL) so that all this exporting to Access could be bypassed?
"Darryl Kerkeslager" <ke*********@co mcast.netwrote in
news:9r******** *************** *******@comcast .com:
"Tom van Stiphout" <no************ *@cox.netwrote
>RegEx is not inherently supported in T-SQL.
Is there any type of regular expression parsing that can be used?
>In the larger picture, parsing is a middle-tier activity, and does not really belong in the data layer.
Perhaps I should have framed my question differently. What I am
trying to accomplish is a one-time data cleanup, some 8 months hence.
Heck, it may take that long to perfect the code. I am testing the
process by getting an export of 49K distinct rows from SQL Server ->
Excel ->Access -Excel, just so that the spreadsheet can serve as a
lookup table for the conversion process. My process will run a day
before the conversion, to provide the lookup of Distinct value;
conversion will actually use the lookup table on 3M+ rows.
Obviously, this is a kluge which would be far better if the data
lookup table could just be created in SQL Server - but I'm not the SQL
Server person, I'm just writing the regular expressions. So the
question is, how would the SQL Server person run my VBA code (or
convert it to another runnable format, like T-SQL) so that all this
exporting to Access could be bypassed?
SQl 2000 and later can use User Defined Functions written within the T-SQL
framework. They look and behave very much like VBA functions. At the bottom
of this message is the first one I wrote; this may explain its ugliness.
Regardless it works and can be used just (almost just; it must be
preferenced by its owner, that is something like SELECT dbo.ProperCase
(Name) FROM ...,) the same as one can use an Access/VBA UDF in Jet in an
Access application.
SQL 2005 can host Net 2.0 Common Language Runtime. http://msdn.microsoft.com/library/de...l=/library/en-
us/dnsql90/html/sqlclrguidance. asp
This means that it can use Regular Expressions. http://blogs.msdn.com/sqlclr/archive.../29/regex.aspx
I would use these two things together to accomplish what you want to
accomplish.
------
CREATE FUNCTION [dbo].[ProperCase]
(
@VarString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @NewString varchar(8000)
DECLARE @Length int
DECLARE @Position int
DECLARE @CharAtPosition varchar(1)
DECLARE @ASCIIOfChar tinyint
DECLARE @WordStart bit
SET @NewString = ''
SET @Length = LEN (@VarString)
SET @Position = 1
SET @WordStart = 1
WHILE (@Position <= @Length)
BEGIN
SET @CharAtPosition = LOWER(SUBSTRING (@VarString, @Position, 1))
IF (@WordStart = 1)
BEGIN
SET @CharAtPosition = UPPER (@CharAtPositio n)
END
SET @ASCIIOfChar = ASCII(@CharAtPo sition)
IF ((@ASCIIOfChar> 64 AND @ASCIIOfChar<92 ) OR (@ASCIIOfChar>9 6 AND
@ASCIIOfChar<12 3))
SET @WordStart = 0
ELSE
SET @WordStart = 1
SET @NewString = @NewString + @CharAtPosition
SET @Position = @Position + 1
END
RETURN @NewString
END
--
Lyle Fairfield http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm
(just a sad story - read if bored)
"Tom van Stiphout" <no************ *@cox.netwrote
Good luck putting 3M rows in Excel. It's still not clear to me what
you want.
No, just the 49K rows.
If you MUST run the procedure on SQL Server, you may need version
2005, and write a DotNet assembly which is callable from an sproc.
Not an option, but I now have an option that I can eliminate, so thanks for
mentioning it.
Since this is a one-time shot, I would likely use Access to link to
the SQL Server table, and then run RegEx from VBA.
Okay, I guess that is an option. My only one, so far.
--
Darryl Kerkeslager
"Lyle Fairfield" <ly***********@ aim.comwrote
SQl 2000 and later can use User Defined Functions written within the T-SQL
framework. They look and behave very much like VBA functions. At the
bottom
of this message is the first one I wrote; this may explain its ugliness.
Thanks, Lyle. At least it is an option to look at and study. Plenty of
time, really, but I needed some direction to even begin looking.
--
Darryl Kerkeslager This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: andreas |
last post by:
Hi there,
I have the following situation:
1. DOT.NET Application is started
2. DOT.NET Application instantiates Access.Application.8
3. Opens a specified database (MDB)
4. DOT.NET application sets a global variable in the access
application to itself (type object)
5. Runs a macro
|
by: Big Time |
last post by:
I'm looking into buying a book to learn Access VBA programming. I am very
familiar with Access however I am new to programming Access with VBA. Are
there any recommendations for books for beginners? I was looking at
"Beginning Access 2002 VBA" by Dave Sussman however many of the reviews are
not so flattering about this one. Any recommendations are much appreciated.
|
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application. I know VBA pretty well but
have never before needed to do this HTTP/XML/MySQL type functions.
|
by: Konrad Hammerer |
last post by:
Hi!
I have the following problem:
I have a query (a) using another query (b) to get the amount of records
of this other query (b), means:
select count(MNR) as Number from Jahrbuch_Einzelversand_Komplett (while
Jahrbuch_Einzelversand_Komplett is a query itself)
|
by: sajithamol |
last post by:
On the analysis of migrating the MS Access VBA application to .Net, here problem is the exsiting VBA application uses grid control created in Visual basic 6.0 as a OCX.
Is there any possiblity available to use .Net controls inside MS Access VBA ? Like when creating a user control It will be build as dll How do I access that dll inside MS Access VBA application ?.
Using Regasm commandline tool to register that component and It will be...
| |
by: JFKJr |
last post by:
I have an excel file, which has columns C and D grouped together, I am trying to delete blank columns and rows from the excel file, ungroup the columns and import the file to MS Access using Access VBA code.
The following is the Access VBA code I used to delete blank columns and rows in the excel file. But, unfortunately, the resultant excel file still has two columns (C and D) grouped together, so when I am importing the file to MS Access,...
|
by: JFKJr |
last post by:
Hello everyone, I am trying to insert a picture in an excel spreadsheet cell using Access VBA.
The following excel VBA code is working perfectly fine and I am able to insert picture in "A1" cell. But with the following Access VBA code, I am getting "Type Mismatch" error at line #13.
Please kindly let me know how to solve this issue.
Thanks a million in advance!
Excel VBA:
Sub InsertPic()
|
by: JFKJr |
last post by:
Hello everyone, the following is the Access VBA code which opens an excel spreadsheet and creates combo boxes dynamically.
And whenever a user selects a value in a combo box, I am trying to pass the selected value into the corresponding cell in excel spreadsheet and then importing the excel spreadsheet into an Access Table.
But the following code is generating "Run-time error 91: Object variable or With block variable not set" error at...
|
by: JFKJr |
last post by:
Hello everyone, the following Access VBA code opens an excel file and creates textboxes in a given range of cells dynamically. The code attaches "MouseUP" and "Exit" events to the textboxes (using the following "DateTextBox" class module).
Whenever a user enters in to the textbox, the code displays "Please enter date in mm/dd/yyyy format (for ex: 01/01/2009)." message which is accomplished using "MouseUp" event.
And, I used "Exit" event to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |