March 11, 2008 at 6:25 pm
I am trying to use Access 2005 and VB to connect to a local SQL database(Mercy). I then want to extract one record at a time and export the data to a XML page. At this stage I am upto setting up my connection and extracting the SQL data.
I think I have setup my connection string correctly but I am not sure how to write the SQL to fit in with the record set code. Do I have to setup a stored procedure, or just write a select satement? any help would good.
Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Dim rs As Recordset
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=Mercy" & DatabaseName & ";UID=sa" & UserName & ";PWD=****" & UserPassword
Set db = ws.OpenDatabase("Mercy", False, False, strConnection)
Set rs = db.OpenRecordset(F_Pat_Present)
Do While Not rs.EOF
'Put the code here for what to do with the information.
'The field information can be access by the field name
intID = rs!IDField
'Or by the order number it is in the list (starting at 0)
intString = rs.Field(1)
rs.MoveNext
Loop
March 12, 2008 at 6:10 am
Do I have to setup a stored procedure, or just write a select satement?
Yes.
Oh, a little more? Fine.
You can do either. The preferred approach when building applications is to create a stored procedure because you are able to establish a lot more in the way of security than with an ad hoc query and because the stored procedure acts to encapsulate your database code so that you can make modifications to that without having to redeploy your application. Both work.
I have a question though. Do you really want to get one row at a time in this query? TSQL is a set based language and usually you would query for the data you need; 1 row, 100, 1000, 10,000 rows. You're shooting SQL Server in the head to try to force it to handle row by row processing (AKA Row by AGONIZING Row or RBAR). You should run a parameterized query or stored procedure to get your data back and then do any kind of cursored stepping through the data on the client side.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2008 at 7:38 am
Considering you are looking to "export to XML" - you should write the Stored procedure using the FOR XML PATH clause (so that the results are returned as XML). You can then simply stream the output to disk using a simple StreamReader/StreamWriter combination.
FOR XML PATH is described in Book Online here:
http://msdn2.microsoft.com/en-us/library/ms189885.aspx
Again - like Grant described - no row by row processing if you can avoid it (and it sounds like you can). Much too inefficient.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply