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

Jumat, 03 Agustus 2012

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

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@comcast.net>
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]