Impostare la richiesta di parametri in una query Oledb
David
Un foglio excel estrae dati da un db access. Fino alla versione 2003 di Excel ho sempre utilizzato
le funzionalità di importazione dati tramite menu Dati-Importa dati esterni-Nuova query su database.
Ora mi trovo in una situazione mista in cui alcune macchine aprono il foglio nel formato 2003 avendo
installato Office 2007 e questa connessione non funziona in quanto, a quanto vedo, il provider di
connessione Access che nel 2003 si chiamava "Database di Microsoft Access", ora nel 2007 viene
denominato "MS Access Database".
Conseguentemente nel tentativo di aggiornare i dati le macchine con versione 2007 restituiscono un
errore per origine dati non trovata.
Ho aggirato il problema utilizzando la funzione Dati-Importa dati esterni-Importa dati con la quale
ho creato una connessione OleDb sul file access. A questo punto riesco ad eseguire la connessione e
l'aggiornamento dei dati su entrambe le versioni di excel. Resta però un problema legato alla
richiesta dei parametri.
Precedentemente nell'editor delle query avevo impostato la stringa
Where [DtaScdRata] Between [Rate in scadenza dal] and [al]
per richiedere i parametri da passare alla query (si tratta di una selezione su uno scadenzario
clienti). Ora con la query Oledb non riesco a passare i paramentri, o meglio, li riesco a passare
solo se li fisso nella query indicando le date.
Come posso impostare la connessione e/o il costrutto della select per farmi comparire di nuovo le
finestre di input dei due parametri da passare?
Per completezza vi posto la definizione della connessione Oledb presa dall'editor delle query Ole Db
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=\\Server\Scadenzario$\Scadenzario
Clienti.mde;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False
e il costrutto della sql attuale (senza parametri)
SELECT CliFor, RagSoc, NrDoc, DtaDoc, DtaScdRata,TotaleScaduto FROM Partite_Cliente
Completo dicendo che ho provato ad inserire la clausola WHERE DtaScdRata Between ? and ? ma ottengo
il messaggio: "Nessun valore specificato per alcuni parametri necessari"
Grazie
David
Maurizio Borrelli
news:0tk6v392nq290ukjb...@4ax.com:
Ciao David.
Non ho capito se e' questo il tuo problema e se l'hai gia' fatto o no,
ma io, come prima cosa, espliciterei i Parametri nella Query:
PARAMETRS [DtaScdRata] DateTime, [Rate in scadenza dal] ... ;
SELECT ...
FROM ...
WHERE [DtaScdRata] Between [Rate in scadenza dal] and [al] ... ;
(Facci sapere se e eventualmente come hai risolto. Grazie!)
--
Maurizio Borrelli [Microsoft MVP - Excel]
http://www.riolab.org/
Maurizio Borrelli
news:#9GjX3Kl...@TK2MSFTNGP03.phx.gbl:
[...]
> PARAMETRS [DtaScdRata] DateTime, [Rate in scadenza dal] ... ;
[...]
Ciao David.
Mi correggo:
PARAMETERS [DtaScdRata] DateTime, [Rate in scadenza dal] ... ;
David
>"Maurizio Borrelli" wrote in message
>news:#9GjX3Kl...@TK2MSFTNGP03.phx.gbl:
>> "David" wrote in message
>> news:0tk6v392nq290ukjb...@4ax.com:
>
>[...]
>> PARAMETRS [DtaScdRata] DateTime, [Rate in scadenza dal] ... ;
>[...]
>
>Ciao David.
>
>Mi correggo:
>
>PARAMETERS [DtaScdRata] DateTime, [Rate in scadenza dal] ... ;
Ciao Maurizio.
Ti ringrazio innanzitutto della pronta risposta.
In effetti il problema è proprio quello dei parametri, o meglio, quello di istruire la select in
modo tale da propormi la finestra di input per la richiesta degli stessi.
Avevo già provato a inserire la riga che mi hai suggerito prima della select in questo modo
PARAMETERS [Rate in scadenza dal] DateTime, [al] DateTime;
SELECT CliFor, RagSoc, NrDoc, .DtaDoc, DtaScdRata, TotaleScaduto FROM H_Partite_Aperte_TOTALE WHERE
DtaScdRata Between [Rate in scadenza dal] and [al];
ma continuo ad ottenere i seguenti messaggi:
1) se nell'editor della query OleDb imposto come "Tipo Comando" SQL o PREDEFINITO: Nessun valore
specificato per alcuni parametri necessari
2) se come "Tipo Comando" imposto TABELLA: Parametri insufficienti. Previsto 2
In ogni caso, che la select così costruita sia di per sè corretta, non vi sono dubbi. Tanto per
togliermi ogni scrupolo ho provato ad inserire la stessa in nel QBE di Access e tutto funziona
correttamente.
Io credo che da qualche parte nella stringa di connessione OleDb vada indicata la presenza di
parametri.
Ho cercato un po' sul web e ho trovato questa pagina microsoft
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx
dove nel codice per visual basic viene riportato questo esempio
Dim queryString As String = "SELECT * FROM Table1 WHERE Field1 LIKE ?"
Dim command As OleDbCommand = New OleDbCommand(queryString, connection)
command.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a"
Dim reader As OleDbDataReader = command.ExecuteReader()
Ma non riesco ad orientarmi per applicarlo al mio caso
Grazie
David
David
<cut>
dopo infruttuose ricerche credo di aver trovato, sigh!!, il motivo dell'impossibilitą di passare
parametri. Vi indico il link a uso di chi dovesse trovarsi nelle medesime condizioni.
http://office.microsoft.com/it-it/excel/HA010346361040.aspx
si dice chiaramente la creazione di query con parametri tramite provider OleDb con la procedura di
importazione dati di excel non č supportata.
Vorrą dire che impostero la connessione e la richiesta di parametri da una routine vba da richiamare
al posto dell'aggiornamento che usavo precedentemente.
David
Maurizio Borrelli
news:9ce9v39jh3tpkdkmj...@4ax.com:
> <cut>
> dopo infruttuose ricerche credo di aver trovato, sigh!!, il motivo dell'impossibilità di passare
> parametri. Vi indico il link a uso di chi dovesse trovarsi nelle medesime condizioni.
> http://office.microsoft.com/it-it/excel/HA010346361040.aspx
> si dice chiaramente la creazione di query con parametri tramite provider OleDb con la procedura di
> importazione dati di excel non è supportata.
> Vorrà dire che impostero la connessione e la richiesta di parametri da una routine vba da richiamare
> al posto dell'aggiornamento che usavo precedentemente.
Ciao David.
Gia'... Non ci avevo pensato. :-(
Pero' potresti fare anche senza codice. Prova questo esempio col db
Northwind:
DSN=MS Access
Database;DBQ=D:\Northwind.mdb;DefaultDir=D:\;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;
SELECT Ordini.`ID ordine`, Ordini.`Data ordine`, Ordini.`ID stato`
FROM Ordini Ordini
WHERE (Ordini.`ID ordine`=?)
In una cella il valore del parametro.
(Facci sapere se e eventualmente come hai risolto. Grazie!)
--
Maurizio Borrelli [Microsoft MVP]
http://www.riolab.org/
David
mmmmh, se non erro quella che tu mi indichi è la stringa di connessione ODBC ricavata da excel 2007.
Il che mi riporta al mio primo post ossia:
il nome del provider Access è cambiato tra le versioni 2003 e 2007.
Nel 2003 ho la seguente stringa di connessione:
DSN=Database di Microsoft Access; ..............
Nel 2007 invece ho quella che anche tu mi indichi
DSN=MS Access Database; ..........
Conseguentemente salvando la stringa nel "formato" 2003, quando tento l'aggiornamento dei dati da
una macchina con office 2007 ho il risultato di origine dati non trovata. Stesso risultato se salvo
la connessione nella versione 2007 e poi tento l'aggiornamento dei dati con una macchina office
2003.
Pensiero in libertà: ma il nome del provider non può essere modificato? Ossia: da qualche parte deve
essere scritto che il driver per la connessione access si chiama MS Access Database piuttosto che
Database di Microsoft Access. Per esempio nella directory windows ho il file ODBC.INI che contiene
tale informazione. Ho provato a modificarlo ma non sembra sortire effetto (in realtà non ho ancora
provato a riavviare la macchina dopo la modifica). Perchè se così fosse. l'alternativa al codice VBA
mi sembrerebbe un test sulla versione di excel in esecuzione e la modifica, temporanea, del nome del
provider di connessione e successivo ripristino al termine dell'aggiornamento.
Che ne pensi?
Ciao
David
Maurizio Borrelli
news:vqj9v39vstl2124kc...@4ax.com:
> Il Thu, 3 Apr 2008 12:19:31 +0000, "Maurizio Borrelli" ha scritto:
[...]
> mmmmh, se non erro quella che tu mi indichi è la stringa di connessione ODBC ricavata da excel 2007.
> Il che mi riporta al mio primo post ossia:
> il nome del provider Access è cambiato tra le versioni 2003 e 2007.
> Nel 2003 ho la seguente stringa di connessione:
> DSN=Database di Microsoft Access; ..............
> Nel 2007 invece ho quella che anche tu mi indichi
> DSN=MS Access Database; ..........
> Conseguentemente salvando la stringa nel "formato" 2003, quando tento l'aggiornamento dei dati da
> una macchina con office 2007 ho il risultato di origine dati non trovata. Stesso risultato se salvo
> la connessione nella versione 2007 e poi tento l'aggiornamento dei dati con una macchina office
> 2003.
> Pensiero in libertà: ma il nome del provider non può essere modificato? Ossia: da qualche parte deve
> essere scritto che il driver per la connessione access si chiama MS Access Database piuttosto che
> Database di Microsoft Access. Per esempio nella directory windows ho il file ODBC.INI che contiene
> tale informazione. Ho provato a modificarlo ma non sembra sortire effetto (in realtà non ho ancora
> provato a riavviare la macchina dopo la modifica). Perchè se così fosse. l'alternativa al codice VBA
> mi sembrerebbe un test sulla versione di excel in esecuzione e la modifica, temporanea, del nome del
> provider di connessione e successivo ripristino al termine dell'aggiornamento.
> Che ne pensi?
Ciao David.
La versione di Excel puo' variare, d'accordo, 2007, 2003, ecc... ma il
database al quale vuoi connetterti varia anche quello? Io avevo capito
che almeno quello fosse fisso, un file *.mde se non sbaglio. Quindi la
seguente stringa di connessione:
ODBC;DBQ=D:\percorso\northwind
2003.mde;DefaultDir=D:\percorso;Driver={Microsoft Access Driver
(*.mdb)};UID=admin;
dovrebbe andar bene in tutti i casi, indipendentemente dalla versione e
dalla lingua.
(Facci sapere se e eventualmente come hai risolto. Grazie!)
--
Maurizio Borrelli [Microsoft MVP - Excel]
http://www.riolab.org/
David
>ODBC;DBQ=D:\percorso\northwind
>2003.mde;DefaultDir=D:\percorso;Driver={Microsoft Access Driver
>(*.mdb)};UID=admin;
Ok, questa la riconosco è una stringa per una connessione DSN-Less. In effetti non ci avevo pensato
a provarla.
Il file di access è sempre lo stesso ed è un mdb in versione 2003. Quello che cambia sono le
versioni di excel che eseguono il foglio XLS che trae dati dal file di access.
Versioni di excel che con una connessione ODBC di tipo DSN fanno i capricci per i motivi che ti ho
illustrato.
Provo il tuo suggerimento in serata e domani ti faccio sapere.
Intanto ti ringrazio per la tenacia!!!
Ciao
David
David
>Ciao David.
>
>La versione di Excel puo' variare, d'accordo, 2007, 2003, ecc... ma il
>database al quale vuoi connetterti varia anche quello? Io avevo capito
>che almeno quello fosse fisso, un file *.mde se non sbaglio. Quindi la
>seguente stringa di connessione:
>
>ODBC;DBQ=D:\percorso\northwind
>2003.mde;DefaultDir=D:\percorso;Driver={Microsoft Access Driver
>(*.mdb)};UID=admin;
>
>dovrebbe andar bene in tutti i casi, indipendentemente dalla versione e
>dalla lingua.
>
>(Facci sapere se e eventualmente come hai risolto. Grazie!)
Buongiorno Maurizio.
Confermo che la tua intuizione sull'utilizzo del DSN-Less funziona egregiamente e quindi la
connessione all'origine dati con relativo aggiornamento avviene eseguendo il foglio excel su
entrambe le versioni di office (con tanto di richiesta parametri!!)
Resta un'ultima difficoltà.
Vorrei che sia il db access che il foglio excel che da questo estrae i dati fossero posizionati su
un percorso di rete. Ho quindi creato la cartella Test sul server e condivisa con tutti i diritti di
accesso (lettura e scrittura) per gli utenti, ho spostato i 2 files e modificato la query da così
(Query funzionante con i file in locale, così come viene salvata dal Query editor di Excel)
XLODBC
1
DBQ=C:\Documents and Settings\....\Test\DB.mdb;DefaultDir=C:\Documents and
Settings\....\Test;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MSAccess;MaxBufferSize=2048;PageTimeout=5;UID=admin;
SELECT Partite_Cliente.DtaScdRata, Partite_Cliente.TotaleScaduto FROM `C:\Documents and
Settings\....\Test\DB`.Partite_Cliente Partite_Cliente WHERE (Partite_Cliente.DtaScdRata Between ?
And ?)
Da DataScadenza a DataScadenza
12 12
DtaScdRata TotaleScaduto
a così
XLODBC
1
DBQ=\\NomeServer\Test\DB.mdb;DefaultDir=\\NomeServer\Test;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MSAccess;MaxBufferSize=2048;PageTimeout=5;UID=admin;
SELECT Partite_Cliente.DtaScdRata, Partite_Cliente.TotaleScaduto FROM
`\\NomeServer\Test\DB`.Partite_Cliente Partite_Cliente WHERE (Partite_Cliente.DtaScdRata Between ?
And ?)
Da DataScadenza a DataScadenza
12 12
DtaScdRata TotaleScaduto
Quando eseguo l'aggiornamento dati mi compare però la finestra di selezione origine dati.
Suggerimenti?
Ciao
David
Maurizio Borrelli
news:pbpbv39qckn5tfoar...@4ax.com:
Ciao David.
> Confermo che la tua intuizione sull'utilizzo del DSN-Less funziona egregiamente e quindi la
> connessione all'origine dati con relativo aggiornamento avviene eseguendo il foglio excel su
> entrambe le versioni di office (con tanto di richiesta parametri!!)
Evviva! :-)
> Resta un'ultima difficoltà.
Te pareva. :-(
Intanto toglierei dalla stringa della query l'indirizzo del db. Cosi':
SELECT Partite_Cliente.DtaScdRata, Partite_Cliente.TotaleScaduto
FROM Partite_Cliente Partite_Cliente
WHERE (Partite_Cliente.DtaScdRata Between ? And ?)
(Un problema in meno!)
Poi considera che anche se modifichi il file della connessione,
l'informazione riguardo il suo percorso memorizzata nella Cartella di
lavoro non cambia.
Io di solito opero, sbrigativamente, tramite la finestra Immediata, se
non ho bisogno di una gestione piu' robusta. L'unica cosa da tener conto
e' che con Excel 2007 e' cambiata la gestione delle QueryTables. Ti
faccio un esempio con Excel 2007.
Finestra Immediata:
?activeworkbook.connections.Count
1
?activeworkbook.connections.Item(1).Name
Connessione
?activeworkbook.connections.Item(1).Type
2
Ovvero:
Const xlConnectionTypeODBC = 2
Membro di Excel.XlConnectionType
Quindi proseguiamo con:
?activeworkbook.connections.Item(1).ODBCConnection.Connection
ODBC;DBQ=D:\percorso\northwind
2003.mde;DefaultDir=D:\percorso;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;
?activeworkbook.connections.Item(1).ODBCConnection.CommandText
SELECT Ordini.IDOrdine, Ordini.DataOrdine, Ordini.Corriere
FROM Ordini Ordini
WHERE (Ordini.IDOrdine=?)
Sempre nella finestra Immediata potrai oltre che leggerli, assegnare
valori a tali proprieta':
activeworkbook.connections.Item(1).ODBCConnection.Connection="<nuova
stringa>"
activeworkbook.connections.Item(1).ODBCConnection.CommandText="<nuova
stringa>"
activeworkbook.connections.Item(1).ODBCConnection.Refresh
Quindi salvare e verificare con Excel 2003.
(Facci sapere se e eventualmente come hai risolto. Grazie!)
--
Maurizio Borrelli [Microsoft MVP]
http://www.riolab.org/
David
Buongiorno Maurizio,
ti ringrazio per i preziosi consigli e spero di non tediarti troppo con questo problema!!!!
Quello che mi dici a proposito della connessione esistente e della modifica del file di connessione
è chiarissimo. E, risolto il problema, sfrutterò le proprietà che mi hai indicato proprio per
"riscrivere" le connessioni nel foglio "reale".
Quello che io sto facendo come test prima di portarlo nell'ambiente finale però è questo:
- sono partito dalla connessione esistente e attraverso il query editor l'ho salvata creandomi un
file con estensione DQY.
- ho modificato quest'ultimo le informazioni contenute in accordo al tuo suggerimento relativo alla
connessione ODBC DSN-Less.
- facendo un doppio click su file così modificato, mi si apre excel che effettua la connessione e
l'estrazione dei dati (con tanto di richiesta dei parametri di estrazione). Ovviamente questo test
l'ho ripetuto (con esito positivo) sia per la versione 2003 che per la versione 2007 di excel.
Il passo successivo, volendo spostare il db in una cartella di rete, è stato quindi quello di
modificare il percorso "locale" trasformandolo in un percorso di rete, sia relativamente al
paramentro "DBQ", che relativamente al parametro "DEFAULTDIR". Ripetendo però il test dopo la
modifica del file .DQY, dopo l'apertura di excel mi compare la finestra di selezione origine dati.
Quindi, al momento, l'ultimo scoglio è proprio questo: come indicare nella connessione
>ODBC;DBQ=D:\percorso\northwind
>2003.mde;DefaultDir=D:\percorso;Driver={Microsoft Access Driver
>(*.mdb)};DriverId=25;FIL=MS
>Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;
al posto di
D:\percorso\northwind2003.mde
qualcosa del tipo
\\nomeserver\nomecartella\northwind2003.mde.
Sembrerebbe, e sottolineo sembrerebbe, che il percorso di rete venga accettato solo in una
connessione ODBC con DSN presente.
^^^
Grazie ;-)
David