|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi all,
I would like to connect to a SQL-server and promt the user with password and
username.
Can I use something like?:
Dim oConn As Object
Set oConn=??
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
"Server=MyServer;" & _
"DataBase=MyDb"
End Sub
.... what do I have to do?
/Kent J.
|
|
hi Kent,
Kent Johnson wrote:
[Quoted Text] > I would like to connect to a SQL-server and promt the user with password and
> username.
In a .mdb or .adp? For normal table access or for passthrough queries only?
mfG
--> stefan <--
|
|
"Stefan Hoffmann" <stefan.hoffmann[ at ]explido.de> wrote in message
news:u1wipbJ3GHA.2096[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > hi Kent,
>
> Kent Johnson wrote:
> > I would like to connect to a SQL-server and promt the user with password
and
> > username.
> In a .mdb or .adp? For normal table access or for passthrough queries
only?
>
>
> mfG
> --> stefan <--
Hi Stefan,
In a .mdb and for normal table access.
My server is not with a trusted connection.
/Kent J.
|
|
hi Kent,
Kent Johnson wrote:
[Quoted Text] >>> I would like to connect to a SQL-server and promt the user with password
> and
>>> username.
>> In a .mdb or .adp? For normal table access or for passthrough queries
> only?
> In a .mdb and for normal table access.
> My server is not with a trusted connection.
You can use a function like this to create the links:
Public Function TableLinkODBC(ASourceName As String, _
Optional ADestinationName As String = "", _
Optional APrimaryKey As String = "") _
As Boolean
CONNECTION_ODBC.
On Local Error GoTo LocalError
Dim CONNECTION_ODBC As String
TableLinkODBC = False
CONNECTION_ODBC = "ODBC;" & _
"DRIVER={SQL Server};" & _
"SERVER=" & DB_Servername & ";" & _
"DATABASE=" & DB_Databasename & ";" & _
"UID=" & DB_Username & ";" & _
"PWD=" & DB_Password
ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
Else
ADestinationName = UCase(ADestinationName)
End If
If TableExists(ADestinationName) Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete ADestinationName
End If
Debug.Print "+"; ASourceName; "="; ADestinationName
SendKeys "{ESC}"
DoCmd.TransferDatabase acLink, "ODBC", CONNECTION_ODBC, _
acTable, ASourceName, ADestinationName, , False
If APrimaryKey <> "" Then
SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " & _
ADestinationName & "(" & APrimaryKey & ") WITH PRIMARY;"
End If
TableLinkODBC = True
Exit Function
LocalError:
'Error handling.
End Function
Call it e.g. TableLinkODBC "dbo.YourTable", "YourTable", "ID". It will
create a link without storing the credentials.
So when opening such a table the first time, you will be prompted by the
ODBC driver automatically. All other tables using the same
CONNECTION_ODBC string will use this opened connection.
btw, its a tool function (for development only), cause the mix of
QueryDefs and DoCmd is not the best.
mfG
--> stefan <--
|
|
"Stefan Hoffmann" <stefan.hoffmann[ at ]explido.de> wrote in message
news:#xwJ1kV3GHA.1588[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > hi Kent,
>
> Kent Johnson wrote:
> >>> I would like to connect to a SQL-server and promt the user with
password
> > and
> >>> username.
> >> In a .mdb or .adp? For normal table access or for passthrough queries
> > only?
>
> > In a .mdb and for normal table access.
> > My server is not with a trusted connection.
>
> You can use a function like this to create the links:
>
> Public Function TableLinkODBC(ASourceName As String, _
> Optional ADestinationName As String = "", _
> Optional APrimaryKey As String = "") _
> As Boolean
> CONNECTION_ODBC.
>
> On Local Error GoTo LocalError
>
> Dim CONNECTION_ODBC As String
>
> TableLinkODBC = False
>
> CONNECTION_ODBC = "ODBC;" & _
> "DRIVER={SQL Server};" & _
> "SERVER=" & DB_Servername & ";" & _
> "DATABASE=" & DB_Databasename & ";" & _
> "UID=" & DB_Username & ";" & _
> "PWD=" & DB_Password
>
> ASourceName = UCase(ASourceName)
> If ADestinationName = "" Then
> ADestinationName = ASourceName
> Else
> ADestinationName = UCase(ADestinationName)
> End If
>
> If TableExists(ADestinationName) Then
> Debug.Print "-";
> CurrentDbC.TableDefs.Delete ADestinationName
> End If
>
> Debug.Print "+"; ASourceName; "="; ADestinationName
> SendKeys "{ESC}"
> DoCmd.TransferDatabase acLink, "ODBC", CONNECTION_ODBC, _
> acTable, ASourceName, ADestinationName, , False
> If APrimaryKey <> "" Then
> SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " & _
> ADestinationName & "(" & APrimaryKey & ") WITH PRIMARY;"
> End If
>
> TableLinkODBC = True
> Exit Function
>
> LocalError:
> 'Error handling.
>
> End Function
>
> Call it e.g. TableLinkODBC "dbo.YourTable", "YourTable", "ID". It will
> create a link without storing the credentials.
>
> So when opening such a table the first time, you will be prompted by the
> ODBC driver automatically. All other tables using the same
> CONNECTION_ODBC string will use this opened connection.
>
> btw, its a tool function (for development only), cause the mix of
> QueryDefs and DoCmd is not the best.
>
>
> mfG
> --> stefan <--
John,
Thanks for your reply! It looks great!
But I'm having problem with; TableExists(ADestinationName)
When I try to compile the code I get a message: "Function not defined"
We have Access 2000 and I'm not sure if TableExists exist as a function.
I can't find TableExists it in the online help.
/Kent J.
|
|
"Kent Johnson" <08.6044303[ at ]telia.com> wrote in message
news:NmXQg.17459$E02.6564[ at ]newsb.telia.net...
[Quoted Text] >
> "Stefan Hoffmann" <stefan.hoffmann[ at ]explido.de> wrote in message
> news:#xwJ1kV3GHA.1588[ at ]TK2MSFTNGP02.phx.gbl...
> > hi Kent,
> >
> > Kent Johnson wrote:
> > >>> I would like to connect to a SQL-server and promt the user with
> password
> > > and
> > >>> username.
> > >> In a .mdb or .adp? For normal table access or for passthrough queries
> > > only?
> >
> > > In a .mdb and for normal table access.
> > > My server is not with a trusted connection.
> >
> > You can use a function like this to create the links:
> >
> > Public Function TableLinkODBC(ASourceName As String, _
> > Optional ADestinationName As String = "", _
> > Optional APrimaryKey As String = "") _
> > As Boolean
> > CONNECTION_ODBC.
> >
> > On Local Error GoTo LocalError
> >
> > Dim CONNECTION_ODBC As String
> >
> > TableLinkODBC = False
> >
> > CONNECTION_ODBC = "ODBC;" & _
> > "DRIVER={SQL Server};" & _
> > "SERVER=" & DB_Servername & ";" & _
> > "DATABASE=" & DB_Databasename & ";" & _
> > "UID=" & DB_Username & ";" & _
> > "PWD=" & DB_Password
> >
> > ASourceName = UCase(ASourceName)
> > If ADestinationName = "" Then
> > ADestinationName = ASourceName
> > Else
> > ADestinationName = UCase(ADestinationName)
> > End If
> >
> > If TableExists(ADestinationName) Then
> > Debug.Print "-";
> > CurrentDbC.TableDefs.Delete ADestinationName
> > End If
> >
> > Debug.Print "+"; ASourceName; "="; ADestinationName
> > SendKeys "{ESC}"
> > DoCmd.TransferDatabase acLink, "ODBC", CONNECTION_ODBC, _
> > acTable, ASourceName, ADestinationName, , False
> > If APrimaryKey <> "" Then
> > SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " & _
> > ADestinationName & "(" & APrimaryKey & ") WITH
PRIMARY;"
> > End If
> >
> > TableLinkODBC = True
> > Exit Function
> >
> > LocalError:
> > 'Error handling.
> >
> > End Function
> >
> > Call it e.g. TableLinkODBC "dbo.YourTable", "YourTable", "ID". It will
> > create a link without storing the credentials.
> >
> > So when opening such a table the first time, you will be prompted by the
> > ODBC driver automatically. All other tables using the same
> > CONNECTION_ODBC string will use this opened connection.
> >
> > btw, its a tool function (for development only), cause the mix of
> > QueryDefs and DoCmd is not the best.
> >
> >
> > mfG
> > --> stefan <--
>
> John,
>
> Thanks for your reply! It looks great!
> But I'm having problem with; TableExists(ADestinationName)
> When I try to compile the code I get a message: "Function not defined"
> We have Access 2000 and I'm not sure if TableExists exist as a function.
> I can't find TableExists it in the online help.
>
> /Kent J.
>
>
Sorry, it should be to Stefan
/Kent J.
|
|
<snip>>
[Quoted Text] >> Thanks for your reply! It looks great!
>> But I'm having problem with; TableExists(ADestinationName)
>> When I try to compile the code I get a message: "Function not defined"
>> We have Access 2000 and I'm not sure if TableExists exist as a function.
>> I can't find TableExists it in the online help.
>>
I always use Joe Fallon's function
http://groups.google.com/
search for
tableexists Joe Fallon group:microsoft.public.access.*
|
|
hi Kent,
Kent Johnson wrote:
[Quoted Text] > But I'm having problem with; TableExists(ADestinationName)
I'm using this function:
Public Function TableExists(ATableName As String _
) As Boolean
Dim Count As Integer
TableExists = False
CurrentDbC.TableDefs.Refresh
For Count = 0 To CurrentDbC.TableDefs.Count - 1
If CurrentDbC.TableDefs(Count).NAME = ATableName Then
TableExists = True
Exit For
End If
Next Count
End Function
CurrentDbC ist the property proxy to CurrentDb, place it in a normal module:
Private m_CurrentDb As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
mfG
--> stefan <--
|
|
|