By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,963 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

ADODB .Open Problem

P: 38
Hello! I have been reading articles all day and still do not get an explanation WHY is this happening. I'm using ADO in msaccess and set up a connection with ADODB, which I have successfully achieved, but now I want to use the "Movefirst " and "MoveNext" method of the recordset using an unbound text, so I wrote a simple function code in VB. Now my dilema starts here. I think I got it right at first, but when I run the database, I got nothing for an output, I mean nothing shows up in my text field when the NEXT command button is clicked. I placed my code here, so pls. anyone with a good heart to help me.
Thanks!
Expand|Select|Wrap|Line Numbers
  1. Public Sub recordMoveFirst()
  2. Dim conn As ADODB.Connection
  3. Dim sql As String
  4. sql = "SELECT * FROM tblIW49;"
  5. Set conn = New ADODB.Connection
  6. conn.Provider = "Microsoft.Jet.OLEDB.4.0"
  7. conn.Open "C:\MyDocuments\dataPMC.mdb"
  8. conn.Execute sql
  9.  
  10. 'declare a recordset
  11. Dim myTableRS As ADODB.Recordset
  12. Set myTableRS = New ADODB.Recordset
  13. myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  14.  
  15.        'go to start of record
  16.        'If myTableRS.EOF Then
  17.        myTableRS.MoveFirst
  18.        Me.txtWorkorder = myTableRS!Workorder
  19.        Me.txtActivity = myTableRS!ActivityType
  20.        Me.txtCompliant = myTableRS!Compliant
  21.        Me.txtComments = myTableRS!Comments
  22.       ' End If
  23.  
  24.        'close the recordset
  25.        myTableRS.Close
  26.        Set myTableRS.ActiveConnection = Nothing
  27.  
  28.        'and the connection
  29.        conn.Close
  30.        Set conn = Nothing
  31.  
  32. End Sub
Feb 6 '08 #1
Share this Question
Share on Google+
16 Replies


Expert 100+
P: 446
Hi

This works fine! First time too !

I'm still mostly using DAO myself so was interested having a look at this.

I set up a dataPMC.mdb with the one table and four fields you describe. I added three records then closed it.

I created a new form in another 'test-bed' database, added a button, 3 text boxes and a checkbox (for Compliant), then pasted your code under the button.

I thought I was going to have to write a "With myTableRS" around the "Me.TxtWorkOrder = myTableRS!Workorder" bit, but it worked ok without.

Sorry can't help with your problem.

S7
Feb 6 '08 #2

Expert 100+
P: 446
Hi again

As a Post Script; I could not understand why you needed to run some sql when setting up the Connection, then separately setting up a Recordset to open the table.

The answer is "you don't"; I commented out the "conn.Execute sql" and it still works fine.

I was curious to open a 'query' rather than a table so I modified your 'open recordset' statement to refer to 'sql' rather than "tbliw49" and it would not work, saying 'Error in FROM clause'. I then noted that you are using a constant 'adCmdTable' which is obviously wrong when running a query, but when I looked-up to find what to change it to, my 'Help' says it should not be there anyway. This works;-

Expand|Select|Wrap|Line Numbers
  1.  
  2. Set myTableRS = New ADODB.Recordset
  3. 'myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  4. myTableRS.Open sql, conn, adOpenDynamic, adLockPessimistic
  5.  
And so does your original line referencing the table, without adCmdTable.
I'm running Access 2002 on this computer so perhaps the 'adCmdTable' is a more recent addition.

I hope you can get you code working !
Regards,
S7
Feb 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,709
Please remember to provide a meaningful Title for any threads started (Please Use Appropriate Titles for New Threads!). This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions.

ADMIN.
Feb 6 '08 #4

P: 38
Hi again

As a Post Script; I could not understand why you needed to run some sql when setting up the Connection, then separately setting up a Recordset to open the table.

The answer is "you don't"; I commented out the "conn.Execute sql" and it still works fine.

I was curious to open a 'query' rather than a table so I modified your 'open recordset' statement to refer to 'sql' rather than "tbliw49" and it would not work, saying 'Error in FROM clause'. I then noted that you are using a constant 'adCmdTable' which is obviously wrong when running a query, but when I looked-up to find what to change it to, my 'Help' says it should not be there anyway. This works;-

Expand|Select|Wrap|Line Numbers
  1.  
  2. Set myTableRS = New ADODB.Recordset
  3. 'myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  4. myTableRS.Open sql, conn, adOpenDynamic, adLockPessimistic
  5.  
And so does your original line referencing the table, without adCmdTable.
I'm running Access 2002 on this computer so perhaps the 'adCmdTable' is a more recent addition.

I hope you can get you code working !
Regards,
S7
--------------------------------------------------

Thanks sierra7! your suggestion is definitely appreciated
Feb 7 '08 #5

Jim Doherty
Expert 100+
P: 897
--------------------------------------------------

Thanks sierra7! your suggestion is definitely appreciated

Hi cephalon

I don't see you using the 'movenext' command anywhere in your code you are only ever referring to movefirst so I suspect you are grappling with understanding recordset navigation generally.

The following code comprises of several sub procedures each tied to a command button as you can see that moves as needed. Each sub then calls a sub procedure to display the relevant data in your fields.

The idea here is to define your connection and recordset variable at form level to hold the necessary information for a recordset so that EACH sub procedure can then reference the recordset merely by the myTableRS variable.

The recordset is opened when the form opens and moves to the last record to grab a total recordcount and then immediately moves to the first row and displays your fielded data in the controls straight away.
I havent done anything with the recordcount but there is nothing to stop you throwing that into a variable if you wished and displaying that on the form

Just paste it all into the code behind your form as is and provided you have command buttons named to match the 'cmd' click subs then it will work for you because i created a db at my end replicating your problem. You should now be in a position once pasted (you might want to create yourself a new form for this) to compare what you have there against this and thus maybe understand then why your original was not working

There is no error handling in this in favour keeping the code lines short but obviously a file must exist in the defined path and so on... otherwise this will fail. You can obviously implement your own error handling routines here.


Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Explicit
  3. Dim conn As ADODB.Connection
  4. Dim sql As String
  5. Dim myTableRS As ADODB.Recordset
  6. Dim MyRecordCount As Long
  7. Private Sub cmdFirst_Click()
  8.     myTableRS.MoveFirst
  9.     NavigateToRecord
  10. End Sub
  11. Private Sub cmdPrevious_Click()
  12.     myTableRS.MovePrevious
  13.     NavigateToRecord
  14. End Sub
  15. Private Sub cmdNext_Click()
  16.     myTableRS.MoveNext
  17.     NavigateToRecord
  18. End Sub
  19. Private Sub cmdLast_Click()
  20.     myTableRS.MoveLast
  21.     NavigateToRecord
  22. End Sub
  23. Private Sub Form_Open(Cancel As Integer)
  24.     sql = "SELECT * FROM tblIW49;"
  25.     Set conn = New ADODB.Connection
  26.     conn.Provider = "Microsoft.Jet.OLEDB.4.0"
  27.     conn.Open "C:\MyDocuments\dataPMC.mdb"
  28.     Set myTableRS = New ADODB.Recordset
  29.     myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  30.     If Not (myTableRS.BOF Or myTableRS.EOF) Then
  31.         myTableRS.MoveLast
  32.         MyRecordCount = myTableRS.RecordCount
  33.         myTableRS.MoveFirst
  34.     NavigateToRecord
  35.     End If
  36. End Sub
  37. Private Sub Form_Unload(Cancel As Integer)
  38.     myTableRS.Close
  39.     Set myTableRS = Nothing
  40.     conn.Close
  41.     Set conn = Nothing
  42. End Sub
  43. Private Sub NavigateToRecord()
  44.     If myTableRS.BOF Then myTableRS.MoveFirst
  45.     If myTableRS.EOF Then myTableRS.MoveLast
  46.     Me.txtWorkOrder = myTableRS.Fields("WorkOrder").Value
  47.     Me.txtActivity = myTableRS.Fields("ActivityType").Value
  48.     Me.txtCompliant = myTableRS.Fields("Compliant").Value
  49.     Me.txtComments = myTableRS.Fields("Comments").Value
  50. End Sub
  51.  
Regards

Jim :)
Feb 7 '08 #6

P: 38
Hi cephalon

I don't see you using the 'movenext' command anywhere in your code you are only ever referring to movefirst so I suspect you are grappling with understanding recordset navigation generally.

The following code comprises of several sub procedures each tied to a command button as you can see that moves as needed. Each sub then calls a sub procedure to display the relevant data in your fields.

The idea here is to define your connection and recordset variable at form level to hold the necessary information for a recordset so that EACH sub procedure can then reference the recordset merely by the myTableRS variable.

The recordset is opened when the form opens and moves to the last record to grab a total recordcount and then immediately moves to the first row and displays your fielded data in the controls straight away.
I havent done anything with the recordcount but there is nothing to stop you throwing that into a variable if you wished and displaying that on the form

Just paste it all into the code behind your form as is and provided you have command buttons named to match the 'cmd' click subs then it will work for you because i created a db at my end replicating your problem. You should now be in a position once pasted (you might want to create yourself a new form for this) to compare what you have there against this and thus maybe understand then why your original was not working

There is no error handling in this in favour keeping the code lines short but obviously a file must exist in the defined path and so on... otherwise this will fail. You can obviously implement your own error handling routines here.


Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Explicit
  3. Dim conn As ADODB.Connection
  4. Dim sql As String
  5. Dim myTableRS As ADODB.Recordset
  6. Dim MyRecordCount As Long
  7. Private Sub cmdFirst_Click()
  8.     myTableRS.MoveFirst
  9.     NavigateToRecord
  10. End Sub
  11. Private Sub cmdPrevious_Click()
  12.     myTableRS.MovePrevious
  13.     NavigateToRecord
  14. End Sub
  15. Private Sub cmdNext_Click()
  16.     myTableRS.MoveNext
  17.     NavigateToRecord
  18. End Sub
  19. Private Sub cmdLast_Click()
  20.     myTableRS.MoveLast
  21.     NavigateToRecord
  22. End Sub
  23. Private Sub Form_Open(Cancel As Integer)
  24.     sql = "SELECT * FROM tblIW49;"
  25.     Set conn = New ADODB.Connection
  26.     conn.Provider = "Microsoft.Jet.OLEDB.4.0"
  27.     conn.Open "C:\MyDocuments\dataPMC.mdb"
  28.     Set myTableRS = New ADODB.Recordset
  29.     myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  30.     If Not (myTableRS.BOF Or myTableRS.EOF) Then
  31.         myTableRS.MoveLast
  32.         MyRecordCount = myTableRS.RecordCount
  33.         myTableRS.MoveFirst
  34.     NavigateToRecord
  35.     End If
  36. End Sub
  37. Private Sub Form_Unload(Cancel As Integer)
  38.     myTableRS.Close
  39.     Set myTableRS = Nothing
  40.     conn.Close
  41.     Set conn = Nothing
  42. End Sub
  43. Private Sub NavigateToRecord()
  44.     If myTableRS.BOF Then myTableRS.MoveFirst
  45.     If myTableRS.EOF Then myTableRS.MoveLast
  46.     Me.txtWorkOrder = myTableRS.Fields("WorkOrder").Value
  47.     Me.txtActivity = myTableRS.Fields("ActivityType").Value
  48.     Me.txtCompliant = myTableRS.Fields("Compliant").Value
  49.     Me.txtComments = myTableRS.Fields("Comments").Value
  50. End Sub
  51.  
Regards

Jim :)


HI JIM!!!!
first of THANKS 100X!!!!
Feb 7 '08 #7

P: 38
You're a real lifesaver, I found my self, dumbfounded and very insecure, to be true. I now know that using unbound objects are not for beginners, unless there’s a guiding hand to lead you, well once again I'm in debt to you and this forum rock!
Thanks guys!
Feb 7 '08 #8

Expert 100+
P: 446
Hi Jim

The same thought struck me, "How's he going to move to next record" so built a mini system much along these lines.

A good idea to put the checks for BOF/EOF in the NavigateToRecord sub rather than under each button though. This was an excuse to play with ADO rather than DAO.

I see that you have kept in the term 'adCmdTable'
Expand|Select|Wrap|Line Numbers
  1.  
  2. myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  3.  
This does not come up in the 'auto-help' when typing the line (in Access 2002) and as I said in my previous post if you substitute a sql string instead of the table name it causes an error. As I would most likely want to open data filtered and ordered, what do you suggest should be used instead? (Leaving it out works too!)

On a separate issue, I'm going to try and help a guy with some DAO code today (thread title Membership History). If you have time to look over it and do an ADO version that would save him having to add areference to DAO.

Cheers

S7
Feb 7 '08 #9

NeoPa
Expert Mod 15k+
P: 31,709
Hi Jim
...
On a separate issue, I'm going to try and help a guy with some DAO code today (thread title Membership History). If you have time to look over it and do an ADO version that would save him having to add areference to DAO.

Cheers

S7
Sierra, why don't you add a link in here to the other post. That way Jim can find it more easily if he's interested.
Feb 7 '08 #10

Jim Doherty
Expert 100+
P: 897
Hi Jim

The same thought struck me, "How's he going to move to next record" so built a mini system much along these lines.

A good idea to put the checks for BOF/EOF in the NavigateToRecord sub rather than under each button though. This was an excuse to play with ADO rather than DAO.

I see that you have kept in the term 'adCmdTable'
Expand|Select|Wrap|Line Numbers
  1.  
  2. myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
  3.  
This does not come up in the 'auto-help' when typing the line (in Access 2002) and as I said in my previous post if you substitute a sql string instead of the table name it causes an error. As I would most likely want to open data filtered and ordered, what do you suggest should be used instead? (Leaving it out works too!)

On a separate issue, I'm going to try and help a guy with some DAO code today (thread title Membership History). If you have time to look over it and do an ADO version that would save him having to add areference to DAO.

Cheers

S7
A big "yes" for you on the bof/eof observation Sierra nicely spotted :))

Cephalon!!... listen to sierra on that observation please if you notice I have not placed a logical test for the row position in each click sub. It IS used in the on 'open event' and a flavour of that called when calling to the NavigateToRecord sub but this NavigateToRecord sub is called 'after' the move commands. It is feasible that the move command could unexpectedly fail potentially for some reason unbeknown to the code and thus generate an untrapped error.

Given the simplicity of the code (it is thrown together as a learning/comparison aide more than a mission critical reliance on error handling) it can be rectified merely by placing an 'on error resume next' statement line prior to the 'move' commands in each sub procedure which will cause the move command to be skipped over and therefore NOT generate an error .....you just don't get any response on screen to the click thats all. I'm not recommending 'on error resume next' heavily here please appreciate as it is in many ways an invitation to laziness for those so inclined (and I do include myself in that :)) ). Code should be trapped for the most part where one is able to do so etc etc. :))

In relation to the acCmdTable constant I interpreted the code as near as one could to the posted code without going off at tangents 'to much' otherwise my perception was a newbie might get confused when comparing against what he knows or has typed out at his /her end.

The recordset is simply that a recordset of the table type which when opened you can do other things with. if you want.... you can then go onto 'filtering and sorting' as sierra mentions.

I prefer however much of the time to open recordsets using an SQL statement that is restricted by a WHERE clause rather than filtering an entire recordset that is first opened and THEN filtered (think if you will for a moment of the data load on huge recordsets. Do we need to open everything then filter? or is it more appropriate to define what we need and only open those records. A 'network' would prefer to know 'which one' has less records that it has to throw around as I'm sure you can appreciate bandwidth blah blah blah and the network guys will luv ya for considering their role in life as will your application on the client side because it will be swifter)

A quick example of the code that would be used as an extension to my last post on the filtering sorting side of things might be this where I'm sure (cephalon) you can see how it might fit in

Expand|Select|Wrap|Line Numbers
  1. myTableRS.Filter = "columnname = 'somestring'"
  2. ' Filter records where the column name is equal to 'somestring' or
  3. ' 'other'
  4. myTableRS.Filter = "columnname = 'somestring' Or columnname = 'a different string'
  5. ' Filter records where record id column is less than 15,000
  6. myTableRS.Filter = "[record id] < 15000"
  7. ' Remove the filter
  8. myTableRS.Filter = ""
  9. ' Sort the records per the column name
  10. myTableRS.Sort = "columnname"
  11. ' Sort them descending
  12. myTableRS.Sort = "columnname Desc" 
  13. ' Sort them by two columns
  14. myTableRS.Sort = "columnname, another_columnname"
  15. ' Remove the sorting
  16. myTableRS.Sort = ""

Sierra..... if I'm around today I might get to the thread you spoke about but have a quite busy schedule today, will have a look later. :)

Regards

Jim :)
Feb 7 '08 #11

Expert 100+
P: 446
Sierra, why don't you add a link in here to the other post. That way Jim can find it more easily if he's interested.
NeoPa
Basically because I am not sure how !

I suppose i could look it up but I am in a bit of a rush today with lots to do before holidays start tomorrow.

While on the topic, I though threads had numbers at one time ?

Also I have noticed just this last week, that if I start a Reply then 'Previous Page' to check details of previous post and return to my 'Reply', its Blank, Gone!

I'm sure this was not the situation a week ago. I now, as a matter of course, Cut & Paste if I have to refer back. An alternative is to 'Preview Post', then it's saved to come back to.

S7
Feb 7 '08 #12

Jim Doherty
Expert 100+
P: 897
Hi Sierra and cephalon,

In response to this bit of your post sierra

"As I would most likely want to open data filtered and ordered, what do you suggest should be used instead?"

and which might be of interest to cephalon as an extension of his thought process in coding and learning the techniques in all of this:

Given we have an example here that is common to us all provided by cephalons datasource which I suspect you replicated your end?

Let us imagine now we added 'another' command button to his form called cmdGetRecords in addition to his navigation buttons

The following code if pasted into the event for the command button will open another instance of the same database and create a new sorted recordset based based a WHERE clause (where workorder is not null) and then immediately export that to a textfile in the same folder as the database. (I thought I'd throw the external file scripting in just for interest sake basically more than anything else to do something with the recordset)

Notice the use in this case of the MyRst.GetString method to retrieve the dataset into the variable myTableRS.........difference method basically nothing more than that in how one might wish to grab and assign an recordset to a variable, in that in this example I want to separate the relevant field data with a tab marker (vbtab) and delineated with a carrige return linefeed (vbcrlf)

None of this is the 'only' way to do things we all know that.. there are a miriad of things we could put in, change or amend and so on... like cursor positions, lock type etc etc. it merely gives a flavour of what one can or could do

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdGetRecords_Click()
  3.     Dim conn As ADODB.Connection
  4.     Dim MyRst As ADODB.Recordset
  5.     Dim myTableRS As Variant
  6.     Dim MyHeader As Variant
  7.     Dim fld As ADODB.Field
  8.     Dim fso As Object
  9.     Dim myFile As Object
  10.     Set conn = New ADODB.Connection
  11.     conn.Provider = "Microsoft.Jet.OLEDB.4.0"
  12.     conn.Open "C:\MyDocuments\dataPMC.mdb"
  13.     Set MyRst = New ADODB.Recordset
  14.     MyRst.Open ("SELECT * FROM tblIW49 WHERE WorkOrder Is Not Null ORDER BY WorkOrder DESC"), conn, adOpenForwardOnly, adLockReadOnly, adCmdText
  15.     If Not (MyRst.BOF Or MyRst.EOF) Then
  16.         'return all rows as a formatted string
  17.         'delimited by tab and rows by control line feed char
  18.         'but first lets grab the field headers
  19.         For Each fld In MyRst.Fields
  20.         MyHeader = MyHeader & fld.Name & vbTab
  21.         Next fld
  22.         'now lets grab the data
  23.         myTableRS = MyRst.GetString(adClipString, , vbTab, vbCrLf)
  24.         'view both elements in the immediate window so we can see what we have here
  25.         Debug.Print MyHeader
  26.         Debug.Print myTableRS
  27.     End If
  28.     'save to a text file
  29.     Set fso = CreateObject("Scripting.FileSystemObject")
  30.     Set myFile = fso.CreateTextFile(CurrentProject.Path & "\MyTextFile.txt", True)
  31.     With myFile
  32.     .writeline MyHeader
  33.     .writeline myTableRS
  34.     .Close
  35.     End With
  36.     'clean up
  37.     MyRst.Close
  38.     Set fso = Nothing
  39.     Set fld = Nothing
  40.     Set MyRst = Nothing
  41.     conn.Close
  42.     Set conn = Nothing
  43. End Sub
  44.  
  45.  


Regards

Jim :)
Feb 7 '08 #13

NeoPa
Expert Mod 15k+
P: 31,709
NeoPa
Basically because I am not sure how !
  1. Prepare a post.
  2. Browse to the link (in another window or Tab) and copy the title.
  3. Switch back to original post and paste in the title.
  4. Switch back to "linked to" thread and now copy the link.
  5. Switch back again to the post and select the title text.
  6. Click on the "globe" button (Insert Hyperlink) and hit enter when it prompts you with the title.
  7. Next it prompts you with "http://" so paste your link in over the top.
  8. Hit enter again and the fully specified link is ready in your post.
...
While on the topic, I though threads had numbers at one time ?
They still do. However, there are a number of valid links to the same thread. Some link by thread number; others by post number. Even within those there are different formats.
Also I have noticed just this last week, that if I start a Reply then 'Previous Page' to check details of previous post and return to my 'Reply', its Blank, Gone!

I'm sure this was not the situation a week ago. I now, as a matter of course, Cut & Paste if I have to refer back. An alternative is to 'Preview Post', then it's saved to come back to.

S7
This might be a recent update. I noticed something myself recently along these lines and lost a lot of hard work in the process. We'll see what happens there.
Feb 7 '08 #14

Expert 100+
P: 446
Hi Jim

This is the link to the other thread where I have given advice in 'DAO'
Membership History

When I first came to ADO I found it overly complicated having to set-up connection strings every time you needed to do something, so I stuck with DAO.

I have subsequently noted that with LINKED table you can use something like 'adActiveConnection' or the like ??

S7
Feb 8 '08 #15

Jim Doherty
Expert 100+
P: 897
Hi Jim

This is the link to the other thread where I have given advice in 'DAO'
Membership History

When I first came to ADO I found it overly complicated having to set-up connection strings every time you needed to do something, so I stuck with DAO.

I have subsequently noted that with LINKED table you can use something like 'adActiveConnection' or the like ??

S7
Hi Sierra

I,ll have a look at the membership thread

You can reference a connection globally if you wish on opening your db and reference that Using the MS Jet 4.0 OLEDB Provider throwing the relevant connection information into a UDL file where you can edit yor connection string without having to revisit your code. They are pretty cool/neat/wicked (whatever the buzzword is lol) .....simply reference the file in a function on startup..an autoexec macro or whatever your design flavour is (Example here is Autoopen) after which you can then reference the connection in your recordsets and so on. Only downside is remembering to cater in your db error trapping strategy to 're-initialise' the CN variable shown below given an untrapped error can drop the connection variable.... soooo what one gets in one hand can have taken away in the other so to speak

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Explicit
  3. Global cn As ADODB.Connection
  4.  
  5. Function Autoopen()
  6. 'call the sub that makes global the connection
  7. DefineMainConnection
  8. 'after which you might want to do some other stuff within this function
  9. 'like logging in or user validation, opening main menu forms etc etc
  10. End Function
  11.  
  12. Public Sub DefineMainConnection()
  13. ' Add a reference to the Microsoft ActiveX Data Objects Library
  14.     Set cn = New ADODB.Connection
  15.     cn.ConnectionString = "File Name=" & CurrentProject.Path & "\myfile.udl"
  16.     cn.Open
  17.     ' cn is now an open connection...
  18. End Sub
  19.  
You might want to have a look at ADezii's very helpful 'howto' article on this for a more detailed breakdown?

How to Create a Data Link File

Regards

Jim :)
Feb 8 '08 #16

P: 1
I have been having an intermitent problem with ADODB.open, after three days of disecting the code I have found the following 'bug' in ADODB in general.

If err.number is set to non zero BEFORE your call to ADODB.function (e.g. open, close, execute etc) then the called function will return that error code as its own error.

The simple answer: ALWAYS set err.number = 0 before any calls to ADODB functions that way the error returned, if any, will at least be a relevant one
Mar 21 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.