Dao connection string to sql server through vba (1 Viewer)

MBMSOFT · Apr 3, 2014

I want to connect to sql server using vba code THROUGH DAO without using odbc/dns
I have this :

Code:

    Dim A As Database
    Dim B As TableDef
    Dim C As DAO.Recordset
    Dim mytable As String
    Dim constring As String

  Set B = A.CreateTableDef(mytable)
   B.connect = constring
   B.SourceTableName = mytable
   A.TableDefs.Append B
   Set C = A.OpenRecordset(mytable)

if i use odbc in : constring ="odbc;DSN=mydsn;DATABASE=mydatabase;" it works like follow

Code:

 Set B = A.CreateTableDef(mytable)
   B.connect = "odbc;DSN=mydsn;DATABASE=mydatabase;"
   B.SourceTableName = mytable
   A.TableDefs.Append B
   Set C = A.OpenRecordset(mytable)

i don't like to use odbc to connecto sql server but all others strings what i could found on net don't work like:

"driver=sql server;server=myadress,1433;initial catalog=mydatabase;user id=myidL;PASSWORD=mypwd;"

"Data Source=ipserevr,1433; Network Library=DBMSSOCN;Initial Catalog=mydb;USER ID=mid;PASSWORD=mypwd;"

"provider=sqloledb;data source=myserver;initial catalog=mydb;userid=myid;PASSWORD=mypwd;"
OR SIMILAR

PLS NOTE:
I'M CONNECTING TO SQL SERVER THROUGH VPN CONNECTION
i hope someone will notice where i'm wrong
thanks

Rx_ · Apr 3, 2014

http://www.access-programmers.co.uk/forums/showthread.php?t=246512&highlight=sqlserver+native+client
There is a good rant about Access to SQL Server with many links to check out.

Basically, you can create a one-time Linked Table(s) with code. Once the Access has a Linked Table, just run the queries against the table like normal. It won't be necessary to do it with every query.
The SQL Server Native Client was demonstrated by Microsoft at an access users group for connection to the cloud SQL Serever (AZURE). That process might be easier to find and modify for your request.

Christos99 · Apr 23, 2014

Hi,

The preferred method of connecting to a SQL Server DB from VBA is ADO, not DAO. I have been using DSNless connections from Access VBA to SQL Server for some time with no issues.

Let me know if you want me to post some example code.

Cheers, Chris

MBMSOFT · May 21, 2014

Christos99 said:
Hi,

The preferred method of connecting to a SQL Server DB from VBA is ADO, not DAO. I have been using DSNless connections from Access VBA to SQL Server for some time with no issues.

Let me know if you want me to post some example code.

Cheers, Chris

yes please..
if you have some samples of code for ado connection could be hellpfull for me..
thanks in advance

Christos99 · May 21, 2014

Hi,

For a DSNless connection to a SQL Server DB, try the following:

First add a reference to the ADO library in Tools/References.
setup your own string variables to use in the code below.

Setup ADO connection string:

DIM cnn as ADODB.Connection
Set cnn = New ADODB.Connection

For a trusted Connection, where your user ID has permissions on the SQL Server:
cnn.Open _
ConnectionString:="Provider=SQLOLEDB.1;" & _
"Data Source=" & strServerName & ";Initial Catalog=" & strDatabaseName & _
";TRUSTED_CONNECTION=YES"

For a connection that requires a SQL Server User ID and Password :
cnn.Open _
ConnectionString:="Provider=SQLOLEDB.1;" & _
"Data Source=" & strServerName & ";Initial Catalog=" & strDatabaseName & _
";User Id=" & strSQLUsername & ";Password=" & strSQLPassword

Galaxiom · May 21, 2014

Christos99 said:
The preferred method of connecting to a SQL Server DB from VBA is ADO, not DAO. I have been using DSNless connections from Access VBA to SQL Server for some time with no issues.

I am a big fan of ADO with OLEDB connections myself.

Note that ODBC can be used with ADO too.

However after heavily promoting OLEDB for several years Microsoft is heading back to ODBC. They say they are dropping the Native Client capability from future versions of SQL Server.

MBMSOFT · May 21, 2014

thanks for replay
i've done your way and i opened connection...
now i need to refresh links to sql database..
I used to do this with dao method:

currentdb.tabledefs.delete mytable
set a = currentdb()
set b =a.createtabledef(mytable)
b.connect = connectionstring
b.sourcetablename = mytable
a.tabledefs.append b
a.yabledefs(mytable).refreshlink

So, What is ADO method to create/refresh link...

Christos99 · May 21, 2014

You shouldn't need to do any refreshing. The SQL Server DB is accessible through the connection (depending on your SQL Server permissions).

You can use the ADO Command object to execute a stored procedure on the server or straight SQL statements.
You can retrieve data using the ADO recordset object. Lots of examples of using ADO online.

MBMSOFT · May 21, 2014

anyway don't i should have links in mdb database to be able to work with queries and forms... it seems to must have the linked tables in mdb, so i should create them...?

Christos99 · May 21, 2014

Hi again,

No, you do not need linked tables. The connection operates directly on the SQL Server.

For example, this deletes all records from table T_TEST

Set gcmd = New ADODB.Command
With gcmd
.ActiveConnection = cnn
.CommandTimeout = 0
.CommandType = adCmdText
.CommandText = "DELETE T_TEST"
.Execute lngRecords
End With

Christos99 · May 21, 2014

Sorry - misread.
To use forms and Access queries you will need to attach.
I use a table 'TablesToAttach' which has every table I want to attach to, then some code to reattach. I will dig it out and post.

MBMSOFT · May 21, 2014

that i tried , an i understood that i can run commands after connection is opened like exec sqls... but if i wan't to create query in mdb file or fill sum form controls I need to have link to sql table . i red that i can do that with adox extensions but samples what i found doesn't work should i activate some references... or so

samples what i found are like this

Sub CreateLinkedExternalTable(strTargetDB As String, _
strProviderString As String, _
strSourceTbl As String, _
strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog
' Open a Catalog on the database in which to create the link.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTargetDB

Set tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property to the
' open Catalog to allow access to the Properties collection.
.Name = strLinkTblAs
Set .ParentCatalog = catDB

' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = strProviderString
.Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
End With

' Append the table to the Tables collection.
catDB.Tables.Append tblLink

Christos99 · May 21, 2014

Try this:

Dim db As Database (declare at module level so available in all procedures)

The main code AttachAll (I have all of this in a form with a button to attach all tables) opens a recordset to read each record from your ‘TablesToAttach’ table containing the table in field TableName.
Please put in your own error handling etc.

Sub AttachAll()
Dim tdf As TableDef, aset As Recordset, strConnectString As String

Set db = CurrentDb
Set aset = db.openrecordset(“TablesToAttach”,dbopensnapshot,dbreadonly)

‘ Builds connection string
For Trusted connection
strConnectString = "ODBC;Driver={SQL Server};SERVER=" & strServerName & ";DATABASE=" & strDatabaseName & ";TRUSTED_CONNECTION=YES"

for SQL Seerver + password connection

strConnectString = "ODBC;Driver={SQL Server};SERVER=" & strServerName & ";DATABASE=" & strDatabaseName & _
";UID=" & strSQLUsername & ";PWD=" & strSQLPassword

With aset
Do While Not .EOF
'
' Delete the linked table (only deletes link)
'
On Error Resume Next
db.TableDefs.Delete !TableName
On Error GoTo 0
'
' Link Table
'
LinkTable db, !TableName, strConnectString, !TableName
.MoveNext
Loop
End If
End With

Close aset
Set aset = nothing
Set db = nothing
End Sub

Sub LinkTable(mydb As Database, _
strTablename As String, _
strConnect As String, _
strSourceTable As String)

Dim tdfLinked As TableDef

Set tdfLinked = mydb.CreateTableDef(strTablename)

tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
mydb.TableDefs.Append tdfLinked

End Sub

MBMSOFT · May 21, 2014

i do get message "could not find installable isam"
if connect to sql database wit dsnleess string always get that message...
with ado it is ok i establish connection to sql database with dsnless string
but i can't create link table

Galaxiom · May 22, 2014

MBMSOFT said:
i red that i can do that with adox extensions but samples what i found doesn't work should i activate some references... or so

If you want to keep trying ADOX the required reference is:
Microsoft ActiveX Ext. x.x for DDL and Security

MBMSOFT · May 22, 2014

Galaxiom said:
If you want to keep trying ADOX the required reference is:
Microsoft ActiveX Ext. x.x for DDL and Security

yes i already check that... and i have adox ...
but still have problem with apending tables...
err "could not fin istallable isam"

MBMSOFT · May 22, 2014

to resume all...
i use this code to create link to sql database WITH ADO METHOD..

Function ado4()

Dim Conn As ADODB.Connection
Dim CatDB As ADOX.Catalog
Dim TblLink As ADOX.Table

Set Conn = New ADODB.Connection
Set CatDB = New ADOX.Catalog
Set TblLink = New ADOX.Table

CatDB.ActiveConnection = CurrentProject.Connection

CurrentDb.TableDefs.Delete "LinkTableName"
TblLink.Name = "LinkTableName"
TblLink.ParentCatalog = CatDB

TblLink.Properties("Jet OLEDB:link provider string") = "provider=msdasql.1;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=MyUser;pwd=MyPasword;"
TblLink.Properties("jet oledb:remote table name") = "RemoteTable"
TblLink.Properties("jet oledb:create link") = True

CatDB.Tables.Append TblLink

Set CatDB = Nothing
End Function

at the end i have err message could not find installable ISAM WHEN I try to append link table
IF I USE THIS CODE WITH CREATED DSN THEN IT IS WORK PROPERLY
CONNECTION STRING THEN IS AS FOLLOW
"ODBC;DSN=MyDSN;database=MyDataBase;uid=MyUser;pwd=MyPasword;"

THE SAME HAPPEN WITH DAO METHOD DESCRIBED IN THE NEXT POST

MBMSOFT · May 22, 2014

the same error i have as well when run code with DAO METHOD...

Function dao()

tbl = "MyTable"
CurrentDb.TableDefs.Delete tbl
Set A = CurrentDb()
Set B = A.CreateTableDef(tbl)
B.Connect = "provider=msdasql.1;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=MyUser;pwd=MyPasword;"
B.SourceTableName = tbl
A.TableDefs.Append B
A.TableDefs(baza).RefreshLink

End Function

MBMSOFT · May 22, 2014

FINALLY I found the right way... So I had mistake in connection string to the sqlDB
so the correct string is :
"ODBC;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=MyUser;pwd=MyPasword;"

so both methods DAO and ADO works properly

Thanks to all replies who guide me to find a way to solve this.. it was very important to me to get DSNless connection to SQL DB

FOR FURTHER REFERENCE AND SOMEONE NEEDS HELP I'LL POST BOTH METHODS

ADO METHOD:
CODE

Function ado4()

Dim CatDB As ADOX.Catalog
Dim TblLink As ADOX.Table

Set CatDB = New ADOX.Catalog
Set TblLink = New ADOX.Table

CatDB.ActiveConnection = CurrentProject.Connection

CurrentDb.TableDefs.Delete "LinkTableName"
TblLink.Name = "LinkTableName"
TblLink.ParentCatalog = CatDB

TblLink.Properties("Jet OLEDB:link provider string") = "ODBC;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=My User;pwd=MyPasword;"
TblLink.Properties("jet oledb:remote table name") = "RemoteTable"
TblLink.Properties("jet oledb:create link") = True

CatDB.Tables.Append TblLink

Set CatDB = Nothing
End Function

DAO METHOD CODE

Function dao()

tbl = "MyTable"
CurrentDb.TableDefs.Delete tbl
Set A = CurrentDb()
Set B = A.CreateTableDef(tbl)
B.Connect = "ODBC;driver={SQL Server};Server=MyServer;database=MyDataBase;uid=MyUser;pwd=MyPasword;"
B.SourceTableName = tbl
A.TableDefs.Append B
A.TableDefs(baza).RefreshLink

End Function

Galaxiom · May 23, 2014

Unless you are changing the SourceTableName it should not be necessary to delete the original tabledef in the DAO technique.

Simply change the Connect Property and run the RefreshLink Method.

BTW. If you want the change to appear in the database window you can run RefreshDatabaseWindow.

Dao connection string to sql server through vba (1 Viewer)

Registered User.

Nothing In Moderation

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread