Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 2

[leonepaolo]

Hi,

How do I set this type of connection to current file I'm working on, i.e. vb code in Access. I get an error that the files been locked.

Any suggestions will be very much appreciated.

Thanks in advance,
Paolo

p.s.
I've tried a variety of things, but from VB6 this works, provided the file is closed.
Dim cnJet As New ADODB.Connection
Set cnJet = CreateObject("ADODB.Connection")
cnJet.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyFile.mdb"

 

[cmmrfrds]

Yes, you cannot create a connection like that to the app you have open Exclusive in design mode.
Use.
Set cnJet = CurrentProject.Connection

Also, in Access this
Set cnJet = CreateObject("ADODB.Connection")
is created dynamically at runtime which is necessary in a browser based app like ASP.

In Access, it is better to declare like this.
Dim cnJet as ADODB.Connection
Set cnJet = New ADODB.Connection

 

[leonepaolo]

Thanks cmmrfrds

I like much of the functionality that connection type provides, is there a different connection I can use.

Thanks again,
Paolo

 

[cmmrfrds]

You can setup a separate connection to your mdb but you can't be in exclusive mode to use it. You would need to setup another mdb for testing and then switch back before opening in multi-user mode.

If you have a front end/ back end setup, then you should be able to link to the back end tables in your front end app. Anyhow, this is a better way of implementing your app.

 

[leonepaolo]

Thanks cmmrfrds,

I think I get it. That means that I'd need 2 files, one to store the data and the other to run my applications. Is this right?

Thanks again,
Paolo

 

[cmmrfrds]

Yes, that is the way most people implement since it is less prone to corruption by separating the data out in a separate mdb.

 

[leonepaolo]

Really... I didn't know that. Looks like I have a lot of reprogramming to do, good thing I'm fond of Sub's including opening connections and recordsets.

I have a dangerous question for you: If I recode my CreateConnection sub procedure
From: Set cnJet = CurrentProject.Connection
To: cnJet.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyFile.mdb",
(as I'll now be accessing the data in another file), will my current code still be valid. Aside from doing
cnJet.Execute sSQL,
Set Rs = cnJet.Execute(sSQL),
the only other things include a copy table structure and create table statements.

I also see a new demand for Programatically creating table links at run-time, do you know how to do that? If you do, could you share, please.

I really like to look at as few tables and queries as possible, so I do a lot of creating and dropping at run-time.

Thanks again!

Paolo

 

[JerryKlmns]

strCopyTable = "INSERT INTO CopyOfTable.* " & _
"SELECT OriginTable.* " & _
"FROM OriginTable " & _
"WHERE FieldA='KOYKOY'"

cnJet.Execute strCopyTable,,129 ' adCmdText + adExecuteNoRecords

FieldA should be of text data type that certainly no record has this value 'KOYKOY'. So the resultset is empty and the cnJet Execute method returns no records, thus running faster.

Now the problem with creating a new table could be approached
A] Using an SQL Specific -Data Definition- Query with a CREATE TABLE Statement. But no DEFAULT value feature, (as I 've learned from a post of PHV)
B] The Catalog and Table Object with ADOX. For this you 'll need a reference to Microsoft ADO Ext. 2.x for DDL and Security
C] Or TableDef object with Microsoft DAO 3.x Object Library.

Linking tables is a very well and many times covered issue in Tek-Tips. Its either ADOX or TableDef way

 

[cmmrfrds]

It looks like JerryKlmns covered most of your questions.

Your code to the BE mdb should work as you show.

The only other thing to add it that if you are creating a lot of objects at runtime, then make sure you.
1. destroy all objects when finished.
i.e. Set yourobject = Nothing.
2. Compact and Repair to reclaim space when you exit.