ACCESS FEED: Bls: Bls: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)

Thank you Duane,...

Would you give me an example what exactly you change in design view..?
for example i have a simple query with ODBC Connect Str on Query Properties = ODBC;DSN=Northwind;,
Which part i have to change so the DSN is not needed like you said ? and change to what..?

Regards
Hendra

________________________________
Dari: Duane Hookom <duanehookom@hotmail.com>
Kepada: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Dikirim: Sabtu, 4 Agustus 2012 19:51
Judul: RE: Bls: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)

I typically make a p-t query using a DSN. Then I go into the design and change the connection string so the DSN is not needed. Duane HookomMS Access MVP
> To: MS_Access_Professionals@yahoogroups.com
> From: agesthahendra@ymail.com
> Date: Sat, 4 Aug 2012 14:45:18 +0800
> Subject: Bls: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)
>
> Bill,..
>
> Based on your codes that you had gave, how do to make the recordset that retrieved from the codes into a local table..?
> Why i want to use PTQ is then i can make it to be a source of an Make Table Query, so i can make others query based on that local table.
>
> Regards
> Hendra
>
>
>
> ________________________________
> Dari: Bill Mosca <wrmosca@comcast.net>
> Kepada: MS_Access_Professionals@yahoogroups.com
> Dikirim: Sabtu, 4 Agustus 2012 10:22
> Judul: Bls: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)
>
>
>
> Hendra
>
> So, it is as I predicted. You need to use a DSN file. The code I gave you does not require a DSN. Hence, you are opening a DSN-less connection.
>
> Forget pass-through queries. They are not the answer to security.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, Agestha Hendra <agesthahendra@...> wrote:
> >
> > Bill,..
> >
> > Yes i had tried them....and....they both didn't work,...
> > the process asked a DSN to continue,... :(
> > Thank you Bill,...
> >
> > Regards
> > Hendra
> >
> >
> > ________________________________
> > Dari: Bill Mosca <wrmosca@...>
> > Kepada: MS_Access_Professionals@yahoogroups.com
> > Dikirim: Sabtu, 4 Agustus 2012 3:23
> > Judul: Bls: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)
> >
> >
> > ﾂ
> > Hendra
> >
> > Why don't you give it a try and see if you can create a new query using the methods at those links?
> >
> > Let us know what happens.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Agestha Hendra <agesthahendra@> wrote:
> > >
> > > Thanx a lot Bill,...
> > >
> > > I was trying to search about DSN_Less PTQ too..., i found some :
> > >
> > > http://www.dbforums.com/microsoft-access/1006185-writing-pass-through-queries-vba.html
> > >
> > > and
> > >
> > > http://stackoverflow.com/questions/10744170/how-do-i-create-a-passthrough-query-in-access-using-a-dsn-less-connection
> > >
> > > i have not tried it yet, how do you think about thoose links about...?
> > >
> > > Regards
> > > Hendra
> > >
> > >
> > >
> > > ________________________________
> > > Dari: Bill Mosca <wrmosca@>
> > > Kepada: MS_Access_Professionals@yahoogroups.com
> > > Dikirim: Sabtu, 4 Agustus 2012 0:56
> > > Judul: Bls: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)
> > >
> > >
> > > ﾃつ
> > > Hendra - Important!!!
> > >
> > > Be sure to set the cnn.ConnectionTimeout = 60
> > >
> > > Otherwise, if your code has a syntax error or you misspell either the SQL Server instance or database Access will just freeze up.
> > >
> > > Bill
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > >
> > > > Hendra
> > > >
> > > > I used a windows authentication DSN. That's why there is no password or user ID.
> > > >
> > > > By the way, After spending about 30 minutes searching for a way to create a DSN-less passthrough I've come to the conclusion that it is impossible. Access has to use ODBC. That requires a DSN even if you don't see it in the query's connection string.
> > > >
> > > > On the other hand, you can open a recordset using ADO and that can be DSN-less.
> > > >
> > > > Typical code:
> > > >
> > > > Option Compare Database
> > > > Option Explicit
> > > > Private cnn As ADODB.Connection
> > > > Private rs As ADODB.Recordset
> > > >
> > > > Private Sub Form_Open(Cancel As Integer)
> > > > Dim strSQL As String
> > > > Dim strMSG As String
> > > >
> > > > On Error GoTo err_PROC
> > > >
> > > > Set cnn = New ADODB.Connection
> > > > cnn.ConnectionString = "Provider=sqloledb;Data Source=MySQLInstance;" _
> > > > & "Initial Catalog=MyDatabase;User Id=zzz_test;Password=dirtydog1"
> > > > cnn.ConnectionTimeout = 0
> > > > cnn.Open
> > > >
> > > > strSQL = "SELECT * FROM ErrorLog"
> > > >
> > > > Set rs = New ADODB.Recordset
> > > > rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
> > > >
> > > > Set Me.Recordset = rs
> > > >
> > > > exit_PROC:
> > > > On Error Resume Next
> > > > Exit Sub
> > > >
> > > > err_PROC:
> > > > Dim strErrMsg As String
> > > > Dim lngIcon As Long
> > > >
> > > > strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " _
> > > > & "occurred in procedure Form_Open of " _
> > > > & "VBA Document Form_frm_dbo_Errorlog"
> > > > lngIcon = vbOKOnly + vbInformation
> > > >
> > > > MsgBox strErrMsg, lngIcon, "Error"
> > > >
> > > > Resume exit_PROC
> > > >
> > > >
> > > > End Sub
> > > >
> > > > Private Sub Form_Unload(Cancel As Integer)
> > > > Set rs = Nothing
> > > > Set cnn = Nothing
> > > >
> > > > End Sub
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, Agestha Hendra <agesthahendra@> wrote:
> > > > >
> > > > > Thank you Steve,..
> > > > >
> > > > > May i have your complete codes example to do that,...i'm beginner in VBA,..
> > > > >
> > > > > Regards
> > > > > Hendra
> > > > >
> > > > >
> > > > >
> > > > > ________________________________
> > > > > Dari: Steve Conklin <StephenMConklin@>
> > > > > Kepada: MS Access Pros List <ms_access_professionals@yahoogroups.com>
> > > > > Dikirim: Jumat, 3 Agustus 2012 23:33
> > > > > Judul: RE: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)
> > > > >
> > > > >
> > > > > If you have a linked table to the same database, you can borrow that table's connect property (that's what I usually do).qPT.connect=currentdb.tabledefs("some odbc linked table").connect Steve
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > From: wrmosca@
> > > > > Date: Fri, 3 Aug 2012 15:21:45 +0000
> > > > > Subject: [MS_AccessPros] Re: vba to make Pass through Queries (with no DSN)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ﾃつ
> > > > >
> > > > >
> > > > > ﾃつﾃつ
> > > > > ﾃつﾃつﾃつ
> > > > > ﾃつﾃつﾃつ
> > > > > ﾃつﾃつﾃつ Hendra
> > > > >
> > > > >
> > > > >
> > > > > The easiest way I know is to create a DSN file and use the builder button in the properties box to select it as the connection string. It doesn't actually use the DSN file. It just copies the parameters.
> > > > >
> > > > >
> > > > >
> > > > > A typical connection string to a SQL Server database using windows Authentication would look like this:
> > > > >
> > > > >
> > > > >
> > > > > ODBC;DRIVER=SQL Server;SERVER=InstanceNameGoesHere;DATABASE=DBNameGoesHere;Trusted_Connection=Yes
> > > > >
> > > > >
> > > > >
> > > > > Regards,
> > > > >
> > > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > >
> > > > > http://www.thatlldoit.com
> > > > >
> > > > > Microsoft Office Access MVP
> > > > >
> > > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > > > >
> > > > > My nothing-to-do-with-Access blog
> > > > >
> > > > > http://wrmosca.wordpress.com
> > > > >
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "agesthahendra@" <agesthahendra@> wrote:
> > > > >
> > > > > >
> > > > >
> > > > > > Hi Everyone,...
> > > > >
> > > > > >
> > > > >
> > > > > > I'm trying to write VBA to make a PTQ but with no DSN in the syntax but
> > > > >
> > > > > > using Connection string since connection string don't require a DSN.
> > > > >
> > > > > > Any example to do this would be appreciated...thank you.
> > > > >
> > > > > >
> > > > >
> > > > > > Regards
> > > > >
> > > > > > Hendra
> > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ﾃつﾃつ
> > > > > ﾃつﾃつ
> > > > >
> > > > > ﾃつﾃつ
> > > > > ﾃつﾃつ
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつﾃつ
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]