[zhouma]

I have an Access 2007 database that is currently connected to our company's main database through an ODBC connection. In a week or so, we'll be moving from using the ODBC to ADO. I have figured out how to connect the two using the following ADO connection string:

Private Sub Form_Open(Cancel As Integer)
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=NAME;User Id=ll;Password=LLLLLL;trusted_connection=yes"
conn.ConnectionTimeout = 30
Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.CommandText = "select * from tblName"
Dim rs As ADODB.Recordset

Where I get stuck is how to connect this to tables and queries in my database? Or better yet how to get the above connection string to build a table that I can use using VBA? What VBA or SQL would I use to link the tables that I previously used ODBC to connect to my Access database?

I've tried using the below but it hasn't worked to far:

Dim fieldCount As Integer
fieldCount = rs.Fields.Count
ListView0.Cols = fieldCount + 1
ListView0.AllowUserResizing = flexResizeColumns
ListView0.Rows = 50
For i = 0 To fieldCount - 1
ListView0.TextMatrix(0, i + 1) = rs.Fields(i).Name
Next
rs.MoveFirst
Count = 1
Do While Not rs.EOF
ListView0.TextMatrix(Count, 0) = Count
For i = 0 To fieldCount - 1
ListView0.TextMatrix(Count, i + 1) = rs.Fields(i)
Next
Count = Count + 1
rs.MoveNext
Loop
rs.Close
conn.Close

I know that the connection works b/c I can do a debug print and it prints all of the information in the table into the MS Visual Basic "Immediate Window". I just can't seem to get the data into a manageable form I can use.

So any help is greatly appreciated. Look forward to your response.

Best,
Zhouma

[Rockn]

Have you not tried to just link to the live tables on this database are making the connection to? Create an ODBC connection to it in Control Panel > Administrative Tools > Data Sources and create a connection in there under System DSN. You can then link directly to the tables in the external database and use it in queries. I remind you that linking this way is linking to live data and can delete and edit the data in your external database. Caution is the word of the day.

[zhouma]

Hi Rockn,

I am currently using ODBC to link the live data to my access database now. The IT director will be deleting the ODBC connection from all computers...well let me be more clear...ODBC will be deleted from all computers...so we will not be able to use ODBC to link anything since it will be deleted. The IT director believes that moving towards using an ADO connection string w/ will be safer...so using a ODBC connection will no longer be an option available to me since everything having to do with ODBC will be deleted. The only way to get the data would be through the ADO connection string.

Any ideas on the VBA code used to get the connection string VBA I listed to output the data into a table?

Best,
Zhouma

[Rockn]

I am not sure where the IT director got the idea that an ODBC connection to a back end database would me less secure than an ADO connection, but I guess it's his network. Anyway, once you create the connection and recordset you should be able to manipulate the data in queries any way you want. As long as you do not close the connection and recordset you are golden. To link to your Access tables you also need to include the Access tables and joins to output data from both.

[zhouma]

Ok. Thanks. I will try this tomorrow while I'm at work and definitely let you know if I have any questions.

I'm a little shaky on how to get the SQL database to output into an Access table but will try the queries first.

Best,
Zhouma

[zhouma]

Hi Rockn,

No luck! I think I'm more confused than I was before I asked my question. I did a lot of google-ing and found everything from DSN-less connection (on Doug Steele's website) and other stuff.

Still not sure how to get the connection to make a table. What I would really like is a linked table. If a linked table isn't possible then I would definitely need a query.

I thank you for your patience in advance and ask you to walk me through this step by step if possible to help me figure out why my connection works but I cannot get the data to output into a continuous form. I read somewhere that ADO only outputs data row by row...is this true?

Ok so as I said my connection string works...what I call i connection string is the following:

Private Sub Form_Open(Cancel As Integer)
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=NAME;User Id=ll;Password=LLLLLL;trusted_connection=yes"
conn.ConnectionTimeout = 30
Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.CommandText = "select * from tblName"
Dim rs As ADODB.Recordset

My question is what VBA code comes after this??????? I cannot get my form to output data I'm guessing b/c there isn't a table linked to it in the obvious sense. How would I get the QBE to pick up that a "query" is attached to the form? How do I control the form's record source so that it connects to the query created above?

Best,
Zhouma

PS. I've been trying to get this to work for the past few months on and off...so to say I'm frustrated with this ADO VBA stuff is an understatement...so any help is greatly appreciated.

[zhouma]

I finally figured it out w/ the help of the following link:

http://support.microsoft.com/kb/281998

Had no idea that I could actually bind my ado recordset to the form itself. This has definitely solved my problem with this and opened a new can of worms re: working on the SQL to combine my ado recordset w/ my tables that are already in my Access database. Either way, I feel that that I can definitely do that b/c the hard part's over.

Btw, the code I ended up using was the following for my continuous form:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MySQLServer"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "NorthwindCS"
.Open
End With

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing

End Sub

Now I'll take this time to figure out whether I want to do a shared connection in a module or have each form open the connection each time.

Thanks for your help either way.

Best,
Zhouma