DSN-less communication with SQL Server tables 2

[jimmythegeek]

I have looked everywhere and not been able to find an answer to this question. Is there a way to communicate with a SQL Server database WITHOUT a DSN (linking), and loop through the tables in a particular database, pulling the information you need (field names, datatype, length, etc.) from within Access.

I can do it with Access tables and LINKED SQL Server tables (kind of, as it will only show Access data types, not SQL Server), but I would like to know if there is some cool code somewhere that will talk to the SS database (ADO perhaps) and let me pull properties out of each of the tables within a database. Thanks for any insight.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[PHV]

Take a look at ADOX

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

jimmythegeek,

This should help...

' DSN-less connection to a database.
' Author: Walt Cygan
' (wcygan@macrogroup.net)
'
' This code is copyrighted freeware - use freely, but please leave this
' header intact. Suggestions and comments welcome.
'
Dim objConn as ADODB.Connection
Dim objRS as ADODB.Recordset
Dim strConn as String
Dim strSQL as String
Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

'Create a connection string.
strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _
     "Initial Catalog=Northwind;User Id=MyId;Password=123aBc;"

'If you have to use MS Access, build a connection string this way.
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Data\MappingDatabase.mdb"

'You can find OLEDB providers and connection syntax for all kinds of 
'other data sources including text files.

'The following string is equivalent to the first used for access to
'SQL Server:
strConn = "Provider=SQLOLEDB;Server=MyServer;" & _
     "Database=Northwind;User Id=MyId;Password=123aBc;"

objConn.Open strConn 'will open the database connection.

'An alternate way to open the connection is:
With objConn
    .ConnectionString = strConn
    .ConnectionTimeout = 120
    .CommandTimeout = 120
    .Open
End With

'The ability to change connection properties is
'a major reason to explicitly create a Connection object.

'This can also be done without the connection string as:
With objConn
    .Provider = "SQLOLEDB"
    .DefaultDatabase = "Northwind"
    .Properties("Data Source") = "MyServer"

    .Properties("User Id") = "MyId"
    .Properties("Password") = "123aBc"
    .Open
End With

'Lets create the simplest possible SQL we want to execute.
strSQL = "Select * from tbAddresses "

'You can now open the recordset.
With objRS
    .Open strSQL, objConn
End With

'or
objRS.Open strSQL, objConn

'or without using the connection object
objRS.Open strSQL, strConn, adOpenForwardOnly

'See documentation on ADO to understand the cursor type options 
'(such as adOpenForwardOnly).
'You can now use the data in objRS.
'=====
'Be sure you close and destroy your objects.
objRS.Close
objConn.Close
Set objConn = Nothing
Set objRS = Nothing

More here... DNS-Less


Good Luck...

 

[jimmythegeek]

Thanks HiTech. Maybe I should have explained better. I am aware of how to connect, I am just not familiar with how to loop through the tables withing the database I have connected to, and pull out the properties for those tables (field name, data type, size, etc.). I think I need to find the system tables that hold this information, and use them for my recordset(s).

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[PHV]

Have you considered ADOX ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

I haven't worked much with this, but I've just played a little with a connection like this:

[tt]cn.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=pubs;" & _
"Integrated Security=SSPI"[/tt]

To get all the tables in a specific database (pubs):

[tt]set rsSys=cn.execute("select name from sysobjects where xtype = 'u'",,adcmdtext)[/tt]

Or use openschema:

[tt]set rsTBL = cn.openschema(adschematables, _
array(empty,empty,empty,"table")[/tt]

To retrieve column properties, perhaps

[tt]set rsCol = cn.openschema(adschemacolumns, _
array(empty,empty,rsTBL.fields("TABLE_NAME").value, empty)[/tt]

Using ADOX, it think would give pretty much the same as

[tt]set rs = cn.execute("select * from [" & _
rsTBL.fields("TABLE_NAME").value & "]",,adcmdtext)
for l=0 rsTBL.fields.count-1
debug.print rsTBL.fields(l).name, rsTBL.fields(l).type, _
rsTBL.fields(l).definedsize
next l[/tt]

There's a difference in what they return (ADOX/cn.execute vs openschema). But it's a bit to late in the evening to figure out which is which, hopefully there's something to play with;-)

Roy-Vidar