How can Access 97 define an ADO connection to the current database,(revised)

[leonard webel]

Hi

I'm working with Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
cat.ActiveConnection = cn

and Im not using a recordset, but rather looping through the database
properties ie all tables, all fields and listing their properties in excel
using ADOX.

Craig

 

[Stefan Hoffmann]

hi leonard, craig or who else you are,

stick to your thread.

I'm working with Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
cat.ActiveConnection = cn

See my answer in your other thread.


mfG
--> stefan <--

 

['69 Camaro]

Hi, Craig . . . uh, Leonard . . . uh, Craig.

It's considered rude to abandon a thread when someone has taken the time to
offer you advice. However, if no one has answered your question after two
days, then it's acceptable to start a new thread and repost your question
with an explanation of how far you've gotten in the original discussion.
Please see the following Web page for more information on how to
successfully ask questions in the newsgroups:

http://www.mvps.org/access/netiquette.htm

looping through the database
properties ie all tables, all fields and listing their properties in excel
using ADOX.

Since you're coding this in Excel, you should post your question in an Excel
newsgroup, not an Access newsgroup. However, I'll save you some trouble and
advise you to avoid the ADOX library and use the DAO library instead when
you connect to the Access 97 database application, because Access 97 was
designed for DAO, not ADO. Furthermore, the Excel library doesn't have a
CurrentProject class, so you'll have to throw your current coding attempt
out.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Craig]

Hi

Yes that is a good idea - Ill have a good read through tonight...

Sorry about that I re-posted and didn't know the time frame or the reply!

O sherbet! Ok

You see initially I used excel which was my first choice and I posted the
following in the excel programming group first:

***********************************************************************
Hi,

Im trying to connect to a sqlserver using this:

MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source={ServerName};" & _
"Initial Catalog={DatabaseName};" & _
"User ID={UserName};" & _
"Password={Password};"

But I am getting an error message:

Run-time error '-2147467259 (80004005)':

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied

I have got a System DSN which does connect to the database successfully. It
is using standard SQL server connection. If i could supply the system DSN Im
sure it would work?

Please help
Thanks

********************************************************************




but I could not connect to sql server getting the above error and being
reffered to http://support.microsoft.com/kb/328306 , so i decided to use the
following from access:

Sub test()
Dim a As String
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook

Dim cn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column
Dim lRow As Long

a = "c:\jan.xls"

Set cn = CurrentProject.Connection

Set xlApp = CreateObject("Excel.Application")
Set xlWrkbk = xlApp.Workbooks.Open(a)
xlWrkbk.Application.Visible = True



cat.ActiveConnection = cn

lRow = 0
For Each tbl In cat.Tables
If tbl.Type = "TABLE" Then
For Each clm In tbl.Columns
lRow = lRow + 1
With ActiveSheet
.Cells(lRow, 1).Value = tbl.Name
.Cells(lRow, 2).Value = tbl.Type
.Cells(lRow, 3).Value = clm.Name
.Cells(lRow, 4).Value = clm.Type
.Cells(lRow, 5).Value = clm.DefinedSize
End With
Next
End If
Next tbl
xlWrkbk.Save
Set cn = Nothing
Set xlWrkbk = Nothing

End Sub

But it does not work with access 97! Please can you guys help me out with
the code using DAO ?

Thanks
Craig (My Name is Craig - Its a short story)

 

['69 Camaro]

Hi, Craig.

I have got a System DSN which does connect to the database successfully.
It
is using standard SQL server connection. If i could supply the system DSN
Im
sure it would work?

How about the syntax for DSN connection strings on the following Web page?

http://www.carlprothman.net/Default.aspx?tabid=89#DSN

I'd direct you to Carl's other Web pages for DSN-less connections in case
the DSN connection string doesn't work, but those don't seem to be available
this morning for some reason.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Craig]

Thank You very very VERY much that is what i needed! - its working now!!

 

['69 Camaro]

Thank You very very VERY much that is what i needed! - its working now!!

You're welcome! Glad to help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.