ADO connection to SQL back-end

[TJBernard]

I am working on an MS Access database that will connect to an MS SQL db back-end through ADO code only (no constant connection), so I am using connection strings when it is needed to pull back data, other wise it is not connect to the back-end database.

I am trying to get the code correct, and I believe I am close but missing one piece. Here is an example of what I have so far:

--------------------------------------------------------
Dim cat As New ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

cat.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=KCNTLBASE;DATABASE=Test;UID=testuser;PWD=testpassword"
cat.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cat
cmd.CommandType = adCmdText
cmd.CommandText = "Select MttrNm from dbo.Page0"
cmd.Execute

Set rs = New ADODB.Recordset
rs.Open cmd 'cmd.Execute

MsgBox rs.RecordCount, vbOKOnly, "Test"
'Debug.Print rs.GetString

cat.Close
-------------------------------------

I believe my error is at this line:

cmd.ActiveConnection = cat

I want the cmd to use the connection string. But all the help I find is just the code "CurrentProject.Connection" which only connects me to the MS Access database itself.

Does anyone know what I need to place here so that the cmd is using the correct connection string to the SQL back-end?

Thank you for your time.

TJ Bernard

 

[iago18]

I'm not positive, but try this....

Set cmd = New ADODB.Command
cmd.ActiveConnection = "PROVIDER=SQLOLEDB;SERVER=KCNTLBASE;DATABASE=Test;U ID=testuser;PWD=testpassword"
...

If that doesn't work tell us what the error message that is being displayed....

 

[TJBernard]

I'm not positive, but try this....

Set cmd = New ADODB.Command
cmd.ActiveConnection = "PROVIDER=SQLOLEDB;SERVER=KCNTLBASE;DATABASE=Test;U ID=testuser;PWD=testpassword"
...

If that doesn't work tell us what the error message that is being displayed....

Thank you for your help. I tried what you recommended, and it gave me the same results (which I completely forgot to include in my first message).

After running this code, a Message Box opens up and is to display the recordset record count. Before I changed the connection string, and now again today I am getting the same recordset record count, which is diplaying: -1.

I don't know why it isn't 0, if it isn't finding any records, instead -1.

But I do know there are over 500 records for this simple select statement (Select MttrNm from dbo.Page0).

So some place I am not connectec correctly is my guess. On the SQL side, this specified users has Select, Insert, Update, and Delete rights.

I am not sure what the hold up is, but I will keep working away. If you have any ideas, let me know.

Thank you again for your help.

T.J.

 

[iago18]

I don't think the problem is with the connection string. To test this, instead of asking MsgBox to display the record count have it display a field value from the first record. If the field value comes up then you know the connection to SQL has been established.

If I'm right so far, then I now venture to guess that the problem is with the RecordCount method. This method WON'T work with all recordset types! Most likely the problem is with the CursorType or LockType of the recordset you created.

Try setting these properties of your recordset before opening it:
.CursorType = adOpenStatic
.LockType = adLockReadOnly*
*This one won't allow record changes! It might not be needed and so I wouldn't set it unless I had to. Try only CursorType first.

Hopefully this helps. Look up RecordCount in Access help for more clues.

Also: If record count won't work you could try using the Access function DCount() instead though I'm not positive that will work with SQL backend tables.

 

[TJBernard]

Thank you very much!

You were correct, I changed the rs.RecordCount to rs.Fields(0) and it showed the correct results in a message box. I am estatic!!

I got one more question (and I hope this is it). I have been able to connect to my database and correctly bring back the results I wanted into an ADO RecordSet.

I would like to place the RecordSet into an MS Access table, so I can report against it, or maybe show a search results form based off of this table. Obviously I would need to clear and re-populate the table each time the search is run, and that would be easy enough.

But is there anyway to populate a blank table (already built table, no records, correct field mapping already set-up) with the results from my ADO recordset?

I tried to loop through the recordset and append each record, but was unsuccessful.

If you have any tips on this, it would be greatly appreicated, and thank you for your help.

T.J.

 

[iago18]

2 thoughts:

1. If the data already exists in SQL somewhere, why not create a Linked table in Access that directly attaches to the data in SQL? Then you could simply base Access forms/reports off this linked table. Do do this you need to:

A.) Create a new File DNS connection to the SQL data. This is under Administrative Tools > Data Sources (ODBC) in Win Xp. From there click on the File DNS tab.
B.) Once the File DNS is created you can open Access, right click in the Tables window, select 'Link Tables', change type to ODBC
C.) Find the File DSN in you created and link your table

2. If you are intent on populating a temp table in Access from SQL data, use the keyword INSERT to do this. In the query wizard, change the query type to 'Append' and try appending from one table to another (within Access). After you've got this working, simply change the syntax to insert into your SQL table. Then of course when you're done with the data use a SQL DELETE statement to purge the temp records.

In my opinion, Option 1 is better if you can do it.

 

[TJBernard]

2 thoughts:

1. If the data already exists in SQL somewhere, why not create a Linked table in Access that directly attaches to the data in SQL? Then you could simply base Access forms/reports off this linked table. Do do this you need to:

A.) Create a new File DNS connection to the SQL data. This is under Administrative Tools > Data Sources (ODBC) in Win Xp. From there click on the File DNS tab.
B.) Once the File DNS is created you can open Access, right click in the Tables window, select 'Link Tables', change type to ODBC
C.) Find the File DSN in you created and link your table

2. If you are intent on populating a temp table in Access from SQL data, use the keyword INSERT to do this. In the query wizard, change the query type to 'Append' and try appending from one table to another (within Access). After you've got this working, simply change the syntax to insert into your SQL table. Then of course when you're done with the data use a SQL DELETE statement to purge the temp records.

In my opinion, Option 1 is better if you can do it.

Thank you again.

I played with it, and got the records to append from the recordset into the table I built. The only problem I have left to resolve is supressing the message that says "You are about to append 1 row(s).". If I can supress this message I am finished with what I needed.

The reason I am not going for number 1 is sort of a story. We had an emergency rush project a few weeks back (you know ASAP project) with 500,000 records that were not even that clean. So our SQL developers built a SQL back-end for this. I built an MS Access Data Project .ADP front-end. By the end of the day we had it finished, and tested it out. It was ready to go.

The next day we came into the office and the users could not use the .adp due to security reasons. We had 6 people spend 4 hours looking into security errors and couldn't even duplicate the error, much less fix it. It is not even the first time we have had quirky issues with .adp's so we are going to avoid them. Due to security, it would be faster and easier to just build the connection to SQL through ADO, populate a table, and then work from the recordset.

So I am working to see if this is possible with MS Access, or if we will have to continue to build large .Net projects and/or clunky ASP pages (MS Access is so much quicker and better, if possible).

Thank you again for your help.

T.J.

 

[RV]

>The only problem I have left to resolve is supressing the message that says "You are about to append 1 row(s).". If I can supress this message I am finished with what I needed<

In VBA, you can supress the message by putting in this line before you run an action query:

DoCmd.SetWarnings False

Don't forget to put it on again:

DoCmd.SetWarnings True

RV

 

[TJBernard]

>The only problem I have left to resolve is supressing the message that says "You are about to append 1 row(s).". If I can supress this message I am finished with what I needed<

In VBA, you can supress the message by putting in this line before you run an action query:

DoCmd.SetWarnings False

Don't forget to put it on again:

DoCmd.SetWarnings True

RV

Perfect, and thank you!!!

Thanks to all the help on this message board, an ADO beginner was able to build a small ADO only MS Access database to proto-type for my boss.

It is his decision if we move forward this way, now he can see what this offers for him.

Thank you again!