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]
>
Tidak ada komentar:
Posting Komentar