Data Purging

Hi

We have a requirement for purging the historical data based on the specified criteria.We have a complex data model with Parent Child Hirearchy goes to multiple levels.

Does any body knows information on how data purging is done in production environments with characteristics such as

Data Volume is huge (500 GB)

Multiple level of realtionships (It can go up to 6 levels)

Physical relationship exists between tables

thanks in advance :)

Regards
Samsudeen B

My 20 cents worth:

One of the reasons data purging may be a pain is the dilemma of associated indexes. You need them to locate the records to delete and they can make the whole process slow because they may have to be rebuilt (not forgetting any locking issues).

The other issue would be whether you have cascading referential integrity set up.

I would not embark on an all-at-once deletion. I would do small/medium range deletions and I might also disable any indexes on the underlying tables. I you have cascading referential integrity, all you have to do is just delete the relevant parent records.

DELETE FROM parent_table

WHERE date_column BETWEEN a AND b

GO

DELETE FROM parent_table

WHERE date_column BETWEEN b AND c

GO

.......................

........................

DELETE FROM parent_table

WHERE date_column BETWEEN y AND z

|||

Hi Nathan,

Thanks for the response.

I don't have the cascading referential integrity setup in my model.As I told earlier my model is complex with child tables been refered with multiple foreign keys..

Do you have any idea on how these kind of situations handled in pruging data in production environment.

Thanks,

Samsudeen B

|||

Without cascading referential integrity, you will have to delete data in the correct order to avoid constraint violations. Another headache.

Anyway, in a production environment, you will have to be much more cautious as it is very easy to slow down the server to a crawl due to table locking and excessive transaction log growth.

Here is what I have done before:

1. Script out the DROP and CREATE index statements for the tables concerned.

2. Script out the DROP and CREATE foreign key constraints for all child tables.

3. Make a script containing piece-meal delete statements (deleting a few thousand rows per table per batch starting with the child tables first). You need to understand the dependencies between your tables

During my "off-peak" hours

4. Change the database recovery model to "simple"

5. Disable all Triggers on the tables.

6. Drop the indexes and foreign keys (all in a script)

7. Run the "delete" script. Delete as many rows as possible within my "free" time window.

8. Enable the triggers

9. Run the script to recreate the indexes for the tables

10. Shrink the log file (DBCC SHRINKFILE)

11. Revert to "Full" Recovery model

12. Run a script to update statistics on the tables.

You don't have to delete all the data all at once. You can do this as many times as necessary if you have a very busy server. And you can put all that work (4-12) into a script and make some coffee. My server is not used for at least 4 hours in the night.

If your server never "chills out" then a carefully scripted delete operation and a simple recovery model may be of help. You may also save yourself a few headaches by adding cascading referential integrity to the tables. That way you script only the delete statements for the parent tables.

Data Processing Extension & SQLClient

I have a business requirement that requires implementing column "masks"
depending on the user running the report. It seems as if a DPE would be my
best solution. Ie. I propose modifying the DataReader to blank dataset
columns in specific circumstances. To prove my concept I would need to
experiment with an extension the delivers the same functionality of the
supplied SQLClient extension. However all the examples that I can find are
based on XML files, event log readers etc. etc. Has anyone got code for an
extension that just reads a DB!Hi LiamD,
You can mask some of the fields using multiples alternatives, using or not
DPE;
1.- Stored Procedures (w/o DPE)
http://www.biasecurities.com/blogs/jim/archive/2004/04/22/453.aspx
2.- Web Services (w DPE) http://www.rdlcomponents.com
3.- Plain DPE http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx
And you can skip the whole XML world
Thanks
Jerry
"LiamD" wrote:
> I have a business requirement that requires implementing column "masks"
> depending on the user running the report. It seems as if a DPE would be my
> best solution. Ie. I propose modifying the DataReader to blank dataset
> columns in specific circumstances. To prove my concept I would need to
> experiment with an extension the delivers the same functionality of the
> supplied SQLClient extension. However all the examples that I can find are
> based on XML files, event log readers etc. etc. Has anyone got code for an
> extension that just reads a DB!|||Jerry
Thanks for your reply. Unfortunately none of the DPE's you reference help -
i.e. None of the open an actual DB connection to run the sql from the
commandtext. This is what I want to do.
Liam|||Another alternative is write a SQL Extended SP and use the XML, for example
http://www.rdlcomponents.com/EXSP/default.aspx
You may find other ways to use XML or WS in SQL as well.
Jerry
"LiamD" wrote:
> Jerry
> Thanks for your reply. Unfortunately none of the DPE's you reference help -
> i.e. None of the open an actual DB connection to run the sql from the
> commandtext. This is what I want to do.
> Liam
>

Data migration from MSAccess to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access. The constraint here is that the data needs to be migrated to a completely new schema. I just need to migrate data only and no other objects.

Thanks

Mahesh

Hello,

I am not replying here with any solution as such.

I would like to do same thing.

I have built complete application using MS Access 2003. Some of the highlights of this application are:

Customized login for each user without using User Level Workgroup Security features.

Each user is assigned 1 of 10 different roles. One of the roles is Admin role

Only Admin role has access to database window and all objects like tables, queries, forms, macros, modules etc.

Shift key is disabled so no one can access database window.

Admin can enabled shift key and get temporary access to database window. Shift key gets disabled on exit again.

Application has data capture front-end forms, one-click reports, quick query tool using front-end forms without query grid etc.

Only certain role can add new data, only certain role can edit data, data gets locked after certain time or status of data etc. Only ceratin role can upload/downlaod data etc.

Application is also password protected. Regular user can open application without knowing password as password is integrated in vba code. This password is essential as no one can export data from other database.

Currently all tables are stored in seperate database and linked in main application.

I would like to move all tables to SQL Server Express. I am assuming that by doing this I will be able to secure all tables better and it will also help me increasing size of application beyond 2 GB.

Please let me know step by step process to move Access Tables to SQL server express.

Thanks

|||

Hi Mahesh,

refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1 which is answered.

Welcome on a board Adukio,

using SSMA you may migrate your Access DB to SQL 2005 Refer the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1

I would suggest to refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

|||

Hemant,

Thanks for the reply. In fact i had downloaded the SSMA and trying a few things. I don't see a option where in some kind of a column mapping can be done in this tool. What i mean is that :

Table1 (Access Tale) Table2 (SQL Express Table)

Column1 Column1

Column2 Column2

Column3

If we assume a scenerio like the one above where i need to migrate table from a access table to SQL table , if the number of columns do not match (this is very much possible as my target schema has been completely redesigned) , SSMA fails to migrate the data. So i need to know is there a provision for handling a scenerio like this in SSMA?

Thanks

Mahesh

|||

Hi Mahesha,

SSMA does not handle data transformation, which is what you're wanting to do. (The Access Upsizing Wizard doesn't do this either.) The only SQL tool I know of that can do this is SSIS, wich is not included in SQL Express. If you have another version of SQL Server 2005 available, say SQL Dev, you can use SSIS to create a data transformation.

If you don't have another edition of SQL available, you will need to do this manually. I would suggest migrating the data from Access to a new database on your SQL Server, and then use queries to transform the data into your new tables. If this is a process you have to do regularly you can probably work out a process of pulling data into temporary tables and then appending them to your new schema all using Stored Procedures.

Mike

|||

Thanks Mike, I think that answers my query. The data migration is a one time activity here , so i think i don't really need to use temp tables here. May be i'll go with queries and stored procedures.

Thanks!

Mahesh

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

data in the header

It is my understanding that data items can not exist in a header (or
footer). Yet in multi page reports it is often a requirement to reprint a
heading that has data in it. What is the best way to address this issue?
Thanks,
Garycreate read-only parameter with default value from query and then to use
this parameter in the page header textbox.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gary D." <Dean@.nospam.com> wrote in message
news:%23F1%233OVhEHA.1768@.TK2MSFTNGP10.phx.gbl...
> It is my understanding that data items can not exist in a header (or
> footer). Yet in multi page reports it is often a requirement to reprint a
> heading that has data in it. What is the best way to address this issue?
> Thanks,
> Gary
>|||Lev:
Sorry, but I've looked through the docs (no search capability) and I just
can't figure out what you mean. could you pleas elaborate on what a
read-only parameter is? I have never worked with reporting software before
where putting data in the heading was problem.
Thanks,
Gary
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:uvsl$ZVhEHA.2784@.TK2MSFTNGP09.phx.gbl...
> create read-only parameter with default value from query and then to use
> this parameter in the page header textbox.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Gary D." <Dean@.nospam.com> wrote in message
> news:%23F1%233OVhEHA.1768@.TK2MSFTNGP10.phx.gbl...
> > It is my understanding that data items can not exist in a header (or
> > footer). Yet in multi page reports it is often a requirement to reprint
a
> > heading that has data in it. What is the best way to address this
issue?
> > Thanks,
> > Gary
> >
> >
>|||Here is some info about adding parameters :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
You need to create a parameter with no prompt (If the prompt is left blank,
and a default parameter value is specified, the default value is used, and
the input box for the parameter is not displayed when the user runs the
report), set default value to "From query" and select dataset and field for
default value.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gary D." <Dean@.nospam.com> wrote in message
news:O8oRF2ghEHA.2620@.TK2MSFTNGP10.phx.gbl...
> Lev:
> Sorry, but I've looked through the docs (no search capability) and I just
> can't figure out what you mean. could you pleas elaborate on what a
> read-only parameter is? I have never worked with reporting software
> before
> where putting data in the heading was problem.
> Thanks,
> Gary
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:uvsl$ZVhEHA.2784@.TK2MSFTNGP09.phx.gbl...
>> create read-only parameter with default value from query and then to use
>> this parameter in the page header textbox.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Gary D." <Dean@.nospam.com> wrote in message
>> news:%23F1%233OVhEHA.1768@.TK2MSFTNGP10.phx.gbl...
>> > It is my understanding that data items can not exist in a header (or
>> > footer). Yet in multi page reports it is often a requirement to
>> > reprint
> a
>> > heading that has data in it. What is the best way to address this
> issue?
>> > Thanks,
>> > Gary
>> >
>> >
>>
>|||It works great, but you must add the RS parameter in the stored procedure.
Without this, you'll have an error.
"Lev Semenets [MSFT]" wrote:
> Here is some info about adding parameters :
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
> You need to create a parameter with no prompt (If the prompt is left blank,
> and a default parameter value is specified, the default value is used, and
> the input box for the parameter is not displayed when the user runs the
> report), set default value to "From query" and select dataset and field for
> default value.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gary D." <Dean@.nospam.com> wrote in message
> news:O8oRF2ghEHA.2620@.TK2MSFTNGP10.phx.gbl...
> > Lev:
> > Sorry, but I've looked through the docs (no search capability) and I just
> > can't figure out what you mean. could you pleas elaborate on what a
> > read-only parameter is? I have never worked with reporting software
> > before
> > where putting data in the heading was problem.
> > Thanks,
> > Gary
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:uvsl$ZVhEHA.2784@.TK2MSFTNGP09.phx.gbl...
> >> create read-only parameter with default value from query and then to use
> >> this parameter in the page header textbox.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "Gary D." <Dean@.nospam.com> wrote in message
> >> news:%23F1%233OVhEHA.1768@.TK2MSFTNGP10.phx.gbl...
> >> > It is my understanding that data items can not exist in a header (or
> >> > footer). Yet in multi page reports it is often a requirement to
> >> > reprint
> > a
> >> > heading that has data in it. What is the best way to address this
> > issue?
> >> > Thanks,
> >> > Gary
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Data from Excel into SQL

Please excuse the cross posting I was not sure which newsgroup would produce
the required advice.
I have a requirement to update a SQL table with data from an Excel
worksheet.
Can anyone suggest the best way to do this...ADO??
PWS
There are numerous variables here. Is this a one-time requirement, or an on-going need? Do you
want to write a program, or just use a utility? If programming, what languages are you open to
using?
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would produce the required advice.
> I have a requirement to update a SQL table with data from an Excel worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links:
'Exhaustive list of objects, properties, methos etc.
http://www.devguru.com/Technologies/...cts_index.html
'getting started
http://msdn.microsoft.com/library/de...tml/sqlado.asp
'a good FAQ for commonly experienced problems - beats waiting for
replies on an NG!
http://www.oblique.ch/ms/ADO_Faq.html
And here's some sample code to get you started. Note, there's many ways
to work with ADO and Access - I use RecordSet to retrieve data and
Command to insert or update. Seems to work ok...
HTH
Gareth
'--
Private Const myDBConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB.mdb"
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
'Once we've opened a connection we leave it open!
'This is closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean
'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then
fcnConnectToDB = True
Exit Function
End If
End If
'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
.CursorLocation = adUseClient
.connectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With
End Function
'
'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant
'Check we're connected to the database
fcnConnectToDB
'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic 'adLockBatchOptimistic
.Open mySQL 'cmd
End With
'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close
'tidy up
Set rs = Nothing
'(We leave the connection permanently open - you may like to
' close it afterwards)
End Function
Public Function ADO_UpdateDB(mySQL As String) As Boolean
'Check we're connected to the database
fcnConnectToDB
'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
On Error Resume Next
.Execute
If Err.Number = 0 Then ADO_UpdateDB = True
On Error GoTo 0
End With
'tidy up
Set cmd = Nothing
'(We leave the connection permanently open)
End Function
Paul Smith wrote:
> Please excuse the cross posting I was not sure which newsgroup would produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how to use SQL in Excel's VBA to:
* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.
DAO and ADO files available.
You can also download the demonstration file called "excelsql.zip".
The code is open and commented.
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would
produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks
Andy Wiggins wrote:
> This might be a help for getting data to and from Excel and Access: It
> includes examples of using variables in SQL queries.
> http://www.bygsoftware.com/examples/sql.html
> Or you can get there from the "Excel with Access Databases" section on page:
> http://www.bygsoftware.com/examples/examples.htm
> It demonstrates how to use SQL in Excel's VBA to:
> * create a database,
> * create a table
> * insert records
> * select records,
> * update records,
> * delete records,
> * delete a table,
> * delete a database.
> DAO and ADO files available.
> You can also download the demonstration file called "excelsql.zip".
> The code is open and commented.
>

Data from Excel into SQL

Please excuse the cross posting I was not sure which newsgroup would produce
the required advice.
I have a requirement to update a SQL table with data from an Excel
worksheet.
Can anyone suggest the best way to do this...ADO'?
PWSThere are numerous variables here. Is this a one-time requirement, or an on
-going need? Do you
want to write a program, or just use a utility? If programming, what langua
ges are you open to
using?
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would produ
ce the required advice.
> I have a requirement to update a SQL table with data from an Excel workshe
et.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links:
'Exhaustive list of objects, properties, methos etc.
http://www.devguru.com/Technologies...ects_index.html
'getting started
http://msdn.microsoft.com/library/d...
ado.asp
'a good FAQ for commonly experienced problems - beats waiting for
replies on an NG!
http://www.oblique.ch/ms/ADO_Faq.html
And here's some sample code to get you started. Note, there's many ways
to work with ADO and Access - I use RecordSet to retrieve data and
Command to insert or update. Seems to work ok...
HTH
Gareth
'--
Private Const myDBConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB.mdb"
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
'Once we've opened a connection we leave it open!
'This is closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean
'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then
fcnConnectToDB = True
Exit Function
End If
End If
'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
.CursorLocation = adUseClient
.connectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With
End Function
'
'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant
'Check we're connected to the database
fcnConnectToDB
'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic 'adLockBatchOptimistic
.Open mysql 'cmd
End With
'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close
'tidy up
Set rs = Nothing
'(We leave the connection permanently open - you may like to
' close it afterwards)
End Function
Public Function ADO_UpdateDB(mySQL As String) As Boolean
'Check we're connected to the database
fcnConnectToDB
'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
On Error Resume Next
.Execute
If Err.Number = 0 Then ADO_UpdateDB = True
On Error GoTo 0
End With
'tidy up
Set cmd = Nothing
'(We leave the connection permanently open)
End Function
Paul Smith wrote:
> Please excuse the cross posting I was not sure which newsgroup would produ
ce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how to use SQL in Excel's VBA to:
* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.
DAO and ADO files available.
You can also download the demonstration file called "excelsql.zip".
The code is open and commented.
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would
produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks
Andy Wiggins wrote:
> This might be a help for getting data to and from Excel and Access: It
> includes examples of using variables in SQL queries.
> http://www.bygsoftware.com/examples/sql.html
> Or you can get there from the "Excel with Access Databases" section on pag
e:
> http://www.bygsoftware.com/examples/examples.htm
> It demonstrates how to use SQL in Excel's VBA to:
> * create a database,
> * create a table
> * insert records
> * select records,
> * update records,
> * delete records,
> * delete a table,
> * delete a database.
> DAO and ADO files available.
> You can also download the demonstration file called "excelsql.zip".
> The code is open and commented.
>