UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Connecting To SQL Server Tables, Access 2016    
 
   
azizrasul
post Oct 16 2019, 08:28 AM
Post#1



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Hopeless at SQL server.

I have a file called ML.dsn on my desktop. When I go into my MS Access db, I can link to a SQL server table by selecting ML.dsn.

I have a lot of SQL server tables (and queries) that I want to connect to, so I decided to do this using VBA as I have a MS Access table that contains the names of the SQL server tables (and queries). I am thinking of using

CODE
DoCmd.TransferDatabase acLink,...


I have also been given the name of the SQL server database as "MedicalLeave" and another piece of information called "SQL2008CLS\LEW". Does anyone know what argument values I need after the code above?

--------------------
Aziz
Go to the top of the page
 
arnelgp
post Oct 16 2019, 09:06 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


first you need to know the Table name on SQL server.
next create a function that will create a Link table.
CODE
Public function CreateLinkMSSQL(source_tableName As String, new_TableName As String)
    Const DSN As String = "ODBC;DSN=ML.dsn;"
    Dim db As DAO.Database
    Dim td As DAO.TableDef

    On Error Resume Next
    db.Execute "Drop Table [" & new_TableName & "];"
    On Error Goto Err_Handler
    Set td = db.CreateTabledef()
    With td
        .Name = new_TableName
        .SourceTableName = source_TableName
        .Connect = DSN
    End With
    db.TableDefs.Append td
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow
Resume_here:
    Set td = Nothing
    Set db = Nothing
    Exit Function
Err_Handler:
    Msgbox Err.Number & vbCrLf & Err.Description
    Resume Resume_here
End Function

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
azizrasul
post Oct 16 2019, 09:28 AM
Post#3



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Tried the code but it gave me an error "3151 ODBC--connection to 'ML.dsn' failed." on line

CODE
db.TableDefs.Append td


If I try the same SQL server table manually, it works. See attachment.
Attached File(s)
Attached File  SQL_Server_Connection.zip ( 174.3K )Number of downloads: 12
 

--------------------
Aziz
Go to the top of the page
 
ADezii
post Oct 16 2019, 09:28 AM
Post#4



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


You can also make the required substitutions below to accomplish the Task. In the Example, a Link is dynamically created to the dbo.YourTableName Table in the SQL Server Database named MedicalLeave on the SQL Server SQL2008CLS\LEW. Simply substitute the appropriate SQL Server Table Name as well as the Linked Table Name in the DB Window.
CODE
'*********** USER DEFINED SECTION ***********
Const conSERVER_NAME = "SQL2008CLS\LEW"
Const conDB_NAME = "MedicalLeave"
Const conTABLE_NAME = "dbo.YourTableName"
Const conLINKED_TABLE_NAME = "LinkedTableName"
'********************************************

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};" & _
      "Server=" & conSERVER_NAME & ";Database=" & conDB_NAME & ";Trusted_Connection=Yes", _
       acTable, conTABLE_NAME, conLINKED_TABLE_NAME
      
RefreshDatabaseWindow

This post has been edited by ADezii: Oct 16 2019, 09:29 AM
Go to the top of the page
 
azizrasul
post Oct 16 2019, 09:33 AM
Post#5



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks ADezii, that worked a charm.

--------------------
Aziz
Go to the top of the page
 
ADezii
post Oct 16 2019, 11:45 AM
Post#6



Posts: 2,700
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , good luck with your Project!
Go to the top of the page
 
sys00
post Oct 31 2019, 01:37 AM
Post#7



Posts: 1
Joined: 5-October 18



Hi
When more than 8 tables where I put the names of the tables 8 in the code


QUOTE
'*********** USER DEFINED SECTION ***********
Const conSERVER_NAME = "SQL2008CLS\LEW"
Const conDB_NAME = "MedicalLeave"
Const conTABLE_NAME = "dbo.YourTableName"
Const conLINKED_TABLE_NAME = "LinkedTableName"
'********************************************

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};" & _
"Server=" & conSERVER_NAME & ";Database=" & conDB_NAME & ";Trusted_Connection=Yes", _
acTable, conTABLE_NAME, conLINKED_TABLE_NAME


This post has been edited by sys00: Oct 31 2019, 01:40 AM
Go to the top of the page
 
azizrasul
post Oct 31 2019, 07:18 AM
Post#8



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks. That's useful to know in the future.

--------------------
Aziz
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 10:45 PM