April 6, 2010 at 6:32 am
I use MSAccess for our front end. Most application have direct access to the server but a couple can run from laptops without a network connection. It these cases I maintain local tables of required info. There are several Server tables that I query, but I only do so once when the Access application opens.
Rather than link to a bunch of tables or view I built several Stored Procedures and have one (a single) pass-through query which I change depending on which stored procedure I need. I do this in most programs and it looks clean, one Access query for a boat load of Stored Procedures.
Is a Stored Procedure better than linking to a bunch of tables and views?
I also saw in another post a way to build a server query with ADO on the fly. I need to figure out how to do this. This would be even cleaner because I wouldn't need multiple stored procedures either.
Any ideas for a novice?
Thank you for your help,
April 7, 2010 at 3:07 pm
Ken@Work (4/6/2010)
I use MSAccess for our front end. Most application have direct access to the server but a couple can run from laptops without a network connection. It these cases I maintain local tables of required info. There are several Server tables that I query, but I only do so once when the Access application opens.Rather than link to a bunch of tables or view I built several Stored Procedures and have one (a single) pass-through query which I change depending on which stored procedure I need. I do this in most programs and it looks clean, one Access query for a boat load of Stored Procedures.
Is a Stored Procedure better than linking to a bunch of tables and views?
I also saw in another post a way to build a server query with ADO on the fly. I need to figure out how to do this. This would be even cleaner because I wouldn't need multiple stored procedures either.
Any ideas for a novice?
Thank you for your help,
Here is a link to start looking: http://allenbrowne.com/func-ADO.html#ExecuteADO
Try Google "MS Access ADO"
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
April 7, 2010 at 3:44 pm
Thank you,
I've been reviewing it and there are some things I can definately use.
The down side to using some of these for select, delete, etc... is (I think) I have to give direct permissions to the table, which I try to avoid if possible. In these cases I suppose a stored procedure or view is still, for security reasons, a better choice.
I still have one issue, I would like to Insert (append) from a MSAccess table to a SQL Server table. I have not idea how to do that other than:
1) Allowing Insert access to the table
2) Linking to the table in Access
3) An Access query - as opposed to a stored procedure.
I know in SQL 2008 you can update to a view if the settings are right but we are not using that yet.
Is my only option in this case to expose the table? I stored procedure (via pass-through query), or VBA code would be an excellent option if do-able.
Thank you again for your help,
April 7, 2010 at 11:53 pm
Not sure of the advantage of a single pass-through query that you change as required - personally, I would still create a separate query for each SQL script, then you never have to amend the query before using it.
As for appending data, there would be a few options; you could simply expose the table and allow inserts, but it doesn't sound like you're very keen on that.
You could have som VB code that queries the Access table and inserts into a SQL table. Nice in that you can easily process each record, trap errors, and report back the results.
Alternatively, you could have an Access query that returns a result-set of SQL Insert statements, which you could than send to the SQL server as a pass-through. You could probably even pass all the Insert statements across to SQL and execute them together. Not so easy to trap and report errors.
Another option might be to use a SQL stored-procedure and pass the insert data to that in a pass-through query. I have done something similar, where I pass in XML containing query-options (for building a Select statement), or data for an Insert/Update.
I like the XML approach because; (1) you can pass it multiple records to be inserted/updated (UpSerts), (2) a SQL stored-script provides good flexibility to evolve as requirements change, and (3) you can pass back an XML file containing the processing results/errors.
HTH
Chris
April 8, 2010 at 6:30 am
Your last option sounds great.
However I (honestly) do not know how to have a stored procedure insert more than one record from an Access table. I may have hundreds of records.
Any hints or examples I can start with would be appreciated.
If I can accomplish this I'd love to learn more about the XML UpSert.
Thank you,
Ken
April 8, 2010 at 2:11 pm
One of the first things I was ever taught in SQL was writing a Select statement that returned other formatted SQL statements using data from the original Select. The following is a similar example using an Access query/table;
tblInsertData (Access table);
-TableKey (autonumber, unique)
-Code (text(10))
-Description (text(50))
Some test data for the tblInsertData (TableKey, Code, Description);
1, 'A', 'Apple'
2, 'B', 'Banana'
3, 'C', 'Carrot'
You can then use an Access query that returns a SQL-Insert statement for each record in tblInsertData;
SELECT 'INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES (''' + Code + ''', ''' + Description + ''')'
FROM tblInsertData;
the Access query then returns a data-set (single-column);
INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES ('A', 'Apple')
INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES ('B', 'Banana')
INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES ('C', 'Carrot')
You can then amend your SQL pass-through query to use the generated SQL-Insert statements - either one at a time, or all together (SQL can parse the whole lot together).
This technique works well and I have used it many times to move data around, however, it still sounds like the XML option may be useful to you. I will post an example of that shortly.
🙂
Chris
April 8, 2010 at 3:06 pm
For the XML option, I have attached 3 SQL scripts based on the previous example of a simple Food table.
The scrQuery_Food script is a generic query - you can pass it an XML string specifying various filters to apply, what columns you want returned, and even sort orders. You also have the option of returning the actual data from the query, returning the SQL query itself, or returning and empty data-set (column-headings only).
The other two scripts relate to the UpSert function - the sqlUpSert_Food script actually does all the work (for the Food table), the sqlUpSert script is just a wrapper that allows you to call all UpSerts via the one script.
The UpSert scripts also pass back a ReturnCode to the calling procedure to identify whether the UpSert was successful or not - and, the first error message (in SQL2005 I will change these to return an XML string containing all errors)
A small word of warning - I created these scripts from existing ones, changing them to match the Food table example. I have not retested them so you may find the odd typo/syntax error.
There is a bit to get your head around with these - there are comments and notes throughout, but feel free to ask any questions.
🙂
Chris
April 9, 2010 at 2:23 am
Hi Chris, your last two posts definitely take Access to another level because they tap its potential.
How do you go about amending a SQL pass-through query to use the generated SQL-Insert statements? Is it a manual procedure?
April 11, 2010 at 2:16 pm
I have often used the QueryDef object to amend the SQL-statement for an Access query before using in a report. It's usually just 2-3 lines of VB code to replace the existing SQL.
The following example shows how you might achieve this, using a SQL statement generated by an Access query to then amend a passthrough QueryDef object.
Dim lclADOConnection As ADODB.Connection
Dim lclADORecordset As ADODB.Recordset
Dim lclQueryDef As QueryDef
Dim lclSQL As String
lclSQL = "SELECT 'INSERT INTO sqlTable (Column1, Column2) VALUES (' + tmp.SourceColumn1 + ', ' + tmp.SourceSource2 + ')' AS SQLInsertStatement FROM AccessSourceTable"
lclADOConnection.Open "AccessConnectionString"
lclADORecordset.Open lclSQL, lclADOConnection
Set lclQueryDef = CurrentDb.QueryDefs("myPassthroughQueryName")
While lclADORecordset.EOF = False
lclQueryDef.SQL = lclADORecordset.Fields("SQLInsertStatement")
lclQueryDef.Execute
lclADORecordset.MoveNext
Wend
Set lclADORecordset = Nothing
Set lclADOConnection = Nothing
Set lclQueryDef = Nothing
In the above example the QueryDef object is amended and executed for each INSERT statement. Alternatively, you could concatenate them all into on long SQL statement, amend the QueryDef and execute it once.
Chris
April 12, 2010 at 6:08 am
This is going to take me a while to figure out. I really appreciate your help. Like the previous post says this takes Access, or at least my knowledge of it, to a new level.
I going to have to play with this quite a bit to understand how it works.
Thank you again,
April 12, 2010 at 6:23 am
A question about these statements.
INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES ('A', 'Apple')
INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES ('B', 'Banana')
INSERT INTO sqlTable (SQLCode, SQLDescription) VALUES ('C', 'Carrot')
If I run this though a pass-through query wouldn't I have to grant insert permission on the table directly? I think this main advantage, if I'm correct, is that I would not need to link the SQL table to the Access application.
Am I reading this right?
April 12, 2010 at 1:56 pm
Correct - those INSERT statements would require the user to have Insert permissions on the SQL table. It does eliminate the need to create a linked table in Access.
One option to avoid granting specific permissions to the SQL table (and if you are using SQL2005 or above) I believe you can run a SQL script using a nominated user-account - I read something the other day about the EXECUTE AS statement, but it's not available in SQL2000.
Chris
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply