DNS-Less Connection through VBA

[johncassell]

Hi all, I have a report that works through a UserDSN ODBC connection but I would like to tweak it so that the user doesn't need the ODBC connection setup on the PC. I believe this is called DSN-Less Connection?

I've tried the following code on a foxpro database but get the error 'User-Defined Type Not Defined' on this line (Dim myDb As New ADODB.connection)

Please Login or Register  to view this content.

Hopefully this is something simple that someone could point out for me please? Thanks in advance, John

[buran]

You need to add reference to Microsoft ActiveX Data Object Library x.x. Go to VBE_>Tools->References.. and scroll down.

[johncassell]

Hi, thanks very much for the reply. Could you tell me how I know which x.x version to apply please?

[buran]

not sure what the difference in different version. I usually opt for 2.6 or 2.8. But here
http://www.vbforums.com/showthread.p...=1#post3260240
2.5 is recomended for greater portability on different win versions. if you develop for yourself and don't plan to distribute - any would do.

[AB33]

You can also using late binding which does not require referencing the object and can work with any version of ADO

Please Login or Register  to view this content.

[johncassell]

Hi Buran/AB33, many thanks for your replies. I'm confident that the ActiveX will work but as I'm sure you'll appreciate, if I can get it to work without the need for adding any extra settings (like in AB33s post) then that would save time and be preferable.

AB33 - thanks for the code but I'm not sure how to amend it to include my connection string. Would it be like this..

Please Login or Register  to view this content.

I've tried that but on the line below, I get 'Invalid Procedure Call or Argument'

With ActiveSheet.QueryTables.Add(connection:=myDb, Destination:=Range("A10"), Sql:=sSQL)[/CODE]

Sure I haven't understood the variables here so help! :-)

[BellyGas]

John, if you're a VBA beginner the late binding thing may just be confusing, stick to what Buran said and add a reference to ActiveX 2.8.

[AB33]

Two steps process.
First, open the database. For this, you need a driver. You need to find out what is the driver for "vfpoledb". For instance, I know the drivers for Access database and SQL server. You might be able to find the driver string from the "String connection" site or from your IT.
Unless you can open the database, you can not process to the second step; that is, accessing the source data (Record set)within the data base.

[johncassell]

Hi, I have access to the database. At the moment the driver name and the settings are in my ODBC UserDSN (The driver is a Microsoft Visual FoxpRO Driver). In my first post I mentioned the driver string that I found from connectionstrings.com

"Provider=vfpoledb;Data Source=C:\MyDatabaseFolder\MyDatabaseFile.DBC; Collating Sequence=machine;"

I have no objection to just adding the ActiveX but I feel like I'm close to achieving it without that (and having to do it on all the other PCs) so would like to spend a bit longer on it before I give up!

[buran]

Adding reference to ADO 2.x will not require doing anything on the taret computer.

[AB33]

Well, there is something wrong with the connection line. It could be the filename is wrong. VBA is pointing to the error, it can not open the connection.
You can use early binding for testing a code. Once all is working, you can convert in to late binding.

[johncassell]

Hi again, I think I've just stumbled upon exactly what buran has just mentioned. The 2.x reference seems to attach itself to the workbook and therefore the target computer(s) won't need manual notification of this reference. This is perfect for me (haven't tested on another PC yet but I'm confident). It will be a little while before I can properly test on another PC but I'll be back on to mark as solved if it works.

Thanks very much for your help everyone. Cheers, John

[buran]

the refernce is in the workbook. potential problem would be if the target computer does not have the respective ADO version. However if the Windows version is not very obsolete, ADO 2.x should work

[johncassell]

Great thanks again for your help. My report is working well now and I have an idea to minimize the code a bit but wanted your thoughts before I messed something up. At the moment I have a different sub/procedure for every query I want to run and I started writing each sub with the following code at the beginning

Please Login or Register  to view this content.

But now I am thinking I could simply create a single separate procedure to initiate this connection and the other subs/procedures would be happy with it and use it? Even if this works Im worrying that I should be closing the connection at some point? Would there be any problem if I Opened the connection when the workbook is opened and just ran different querys against it all day long?

Thanks, John (And apologies if I should have started this as a separate thread)

[buran]

I would not open the connection and keep it open. Anyway you need to declare connection and recordset objects in every sub or supply the connection as argument (extra code to call the sub with the query). Anyway the code will not be shorter.
However I would declare connection string once as constant available to all my subs and open connection, run query and close the connection. This way if any changes you will need to make change in only one place.

[johncassell]

Thanks for the advice. I've been playing around and can see that the code won't be any shorter. Yes, I have already set the connection string as a constant (but appreciate the tip!) and I am quite happy to put a .close statement after the query runs but would you mind explaining why this needs to be done. At the moment, I have been running these queries from a copy database on my own PC and everything seems fine but would it cause any issues if I left the connection open once I am querying the live database?

[johncassell]

Thanks for the advice. I've been playing around and can see that the code won't be any shorter. Yes, I have already set the connection string as a constant (but appreciate the tip!) and I am quite happy to put a .close statement after the query runs but would you mind explaining why this needs to be done. At the moment, I have been running these queries from a copy database on my own PC and everything seems fine but would it cause any issues if I left the connection open once I am querying the live database?