Re: Linking tables access - sql server 2005

Hi,

why not use a direct link to the necessary tables instead of ODBC? Another advantage of this method is that you don't need an ODBC setting on the local computer, it's all in the source. Disadvantage is only that you need to save the password inside of the source, but you could use an input dialog if you want.

Here's the code (the first function is from a MS knowledgebase article, see the comments. "fnErr" is a function which handles the errors in my application, exchange it with an own one. The second function uses the first one to link all the needed tables into Access. This works only in ACCDB, not in an ADP, reason shoule be clear...):

'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim fld As Field
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
fnErr "modODBC.AttachDSNLessTable"
End Function

' Function to refresh every linked table
' Date: 17.06.08, Coppes
' Usage: The first array contains the table names used in Access
' The second array contains the table names used in SQL Server
' As the structure of linked tables are not automatically refreshed after
' changing in SQL Server this function also helps to have the most actual structure.
'
' The DSN-less linking avoids the login screen to SQL server the first time a linked table
' is used. Moreover you don't need to install any DSN on the user's machine
'
' The sub is not used in the application and must be started in the "Immediate" window in the VBA editor
'
Public Sub RefreshLinkedTables()
' Change the number of linked tables here if you add or delete tables
Const constNumberOfTables = 3
Dim strAccTables() As String
Dim strSQLTables() As String
Dim i As Long

ReDim strAccTables(constNumberOfTables)
ReDim strSQLTables(constNumberOfTables)

' These are only examples, this are the table names in the way they will appear in the tables list in Access
strAccTables(0) = "tblCustomerSupplier"
strAccTables(1) = "tblCustomerSupplierContact"
strAccTables(2) = "tblBuild"
strAccTables(3) = "tblDelivery"

' These are the table names in the way they appear in the SQL Server.
strSQLTables(0) = "dbo.tblCustomerSupplier"
strSQLTables(1) = "dbo.tblCustomerSupplierContact"
strSQLTables(2) = "dbo.tblBuild"
strSQLTables(3) = "dbo.tblDelivery"

For i = 0 To constNumberOfTables
AttachDSNLessTable strAccTables(i), strSQLTables(i), "EnterYourServerNameHere", "EnterTheNameOfTheSQLServerDatabaseHere", "EnterYourUserNameHere", "EnterYourPasswordForThisUserHere"
Next
End Sub
' -------------------------------


As mentioned, you can exchange the literal password with an input box asking for the password to the server.
So, if you know the names of the tables you need, you can change the values of the stringarrays above, change the constant of the number of tables you used (don't forget that an array begins with a 0), and call "RefreshLinkedTables", that's all.

If the structure of the tables ever changes, you simply only need to call this function again and you're up to date.

Cheers,

Christian


"bugerr" <bugerr@xxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im Newsbeitrag news:0682B6E8-CC4F-4544-ACE5-3131CB25DE17@xxxxxxxxxxxxxxxx
thx Paul, I only need 10 or 20 tables, but i cant select them because when i
am acceeding throught ODBC, the SQL Query only show me the tables in
alphabetical order, and just the tables I need i cant reach then
them

i tried with these drivers:

sql native client 2005.90.4035.00
sql server 2000.85.1132.00

"Paul Shapiro" wrote:

You might check a SAP user group, or SAP technical support. I would not
expect Access to successfully work with 60,000 linked tables. I also would
guess you are not going to do anything in Access with all 60,000 tables at
once. Maybe you can select a subset of the SAP tables that represent an
"area" of the overall SAP database. If you need multiple subsets, you might
be able to use multiple Access databases, one linked to each subset.

"Sylvain Lafontaine" <sylvainlafontaine2009@xxxxxxxx> wrote in message
news:ObVKjrdvJHA.248@xxxxxxxxxxxxxxxxxxxxxxx
> But then, don't forget to provide some more details. For example, are > you
> using the older SQL-Serve ODBC Provider or if you are using the latest
> Native ODBC Provider for SQL-Server, either the version 9.0 (that comes
> with SQL-Server 2005) or 10.0 (that comes with SQL-Server 2008, both
> versions of the native providers will work against other version of
> SQL-Server); see the Microsoft SQL Server 2008 Feature Pack to > separately
> download the version 10 of the Native Provider for SQL-Server:
>
> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
>
> -- > Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam > please)
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "bugerr" <bugerr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:48335891-E5E0-4D14-BAA0-E3CC3F5A40E4@xxxxxxxxxxxxxxxx
>> Unfortunely, the database scheme depends of a SAP system, that >> generate
>> this
>> hell. Thanks for the information, ill try in the other newsgroup
>>
>> "Sylvain Lafontaine" wrote:
>>
>>> The best idea would probably be to reduce the number of tables. With >>> a
>>> number such as 60000 tables, there is about a 99.99999% chance and >>> more
>>> that
>>> the design of your database is not normalized. Reducing this number >>> to
>>> something like around 100 - 300 tables would be OK.
>>>
>>> The second idea would be to ask in a newsgroup more appropriate such >>> as
>>> microsoft.public.access.odbcclientsvr. This newsgroup
>>> (m.p.a.adp.sqlserver)
>>> is about ADP and SQL-Server and has nothing to do with MDB or ACCDB
>>> files
>>> and ODBC linked tables.
>>>
>>> The third idea would be to provide more details about your >>> environment:
>>> which version of Access, which version of database file (MDB or >>> ACCDB),
>>> which version of SQL-Server ODBC provider that you are trying to use
>>> (and
>>> which that you did not try to use); with what method that you are
>>> linking
>>> these; etc.
>>>
>>> Of these three ideas, if I were you, I would lose any time on the >>> second
>>> and
>>> third and I would concentrate exclusively on the first one.
>>>
>>> -- >>> Sylvain Lafontaine, ing.
>>> MVP - Windows Live Platform
>>> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
>>> please)
>>> Independent consultant and remote programming for Access and >>> SQL-Server
>>> (French)
>>>
>>>
>>> "bugerr" <bugerr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>> news:66E8224A-4F14-4EB8-AAFC-ADEDD1D09B1A@xxxxxxxxxxxxxxxx
>>> > Hi all,
>>> >
>>> > I've a sql server 2005 database, with 60.000 tables. I need links
>>> > these
>>> > tables to a access file throught odbc. If i link these with the >>> > odbc
>>> > to a
>>> > sql
>>> > server 2000 i obtain the 100% of tables. But with the link is with >>> > sql
>>> > 2005 i
>>> > only obtain the 40-50% of the tables. I've tried play with the odbc
>>> > parammeters, but nothing happens.
>>> >
>>> > Any idea?. THX



.

Relevant Pages

  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: [VW 7.3.1] ODBCConnection
    ... I played around with a few combinations on the connection string and ... with a trusted SQL Server connection. ... there are differences for database connects. ... > any database specific odbc drivers. ...
    (comp.lang.smalltalk)
  • Re: Need ODBCDirect-ion
    ... Just as a side note, ODBC, or so called "jet" direct is simply a way to ... You need experience with sql server. ... "Upsizing to Microsoft SQL Server" White Paper Available in Download ... Choosing Database Tools White Paper Available in Download Cente ...
    (microsoft.public.access.modulesdaovba)
  • Re: Supporting a variety of database types in an app?
    ... Ralph, thank you for your insightful response. ... To get experience with MS SQL Server and client/server development. ... My rationale for using a file-based database location/selection method: ... ODBC API, but modified it to enhance performance). ...
    (microsoft.public.vb.database)
  • Re: Trusted Connection
    ... HOWEVER, for one of my users, I will create an ODBC DSN ... for my test database ON THEIR COMPUTER, ... So, when I set up the ODBC DSN connection for this user, ... >> Our SQL Server was set up NOT by me, ...
    (microsoft.public.sqlserver.security)