Connecting an Access DB to VB

[RDIXON]

Hey Everyone,

Im trying to connect an access database to a VB project using the VB Wizard option (im new to VB!!) available when you first launch the application.

The problem I am having is when I browse for the database I want to use and click OK an error message is returned:

'Unrecognised database format' and then the name of the file.

The only reason I can think of is that I am accessing Access from a server on a network. We use Citrix Metaframe which changes the image of the file but not the actual file extension in the folder which it is saved in. The file extension is .mdb

Is this a network problem or am I doing something wrong??

Thanks for your time,

Regards,

Russ

 

[Mile-O]

Double Post

 

[RDIXON]

Eh!

Im lost on that one mate?!

 

[Mile-O]

You've already posted the question in one part of the forum - there's no need to post it again as it only clutters the board.

 

[RDIXON]

zzzzzzzzzzzzzzzzzzzzzzzzzzzzz!

 

[Rich]

zzzzzzzzzzzzzzzzzzzzzzzzzzzzz!

Wakey wakey, fancy nodding off online

 

[mission2java_78]

I'll post an example let me just find it.

 

[mission2java_78]

VB Front end access back end ehh?

Its better to use Ado and some functions rather than any wizard.

[vbcode]
Option Explicit
Public objConn As ADODB.Connection
Public Const dbConnectionString As String = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=PATH_OF_YOUR_MDB;" & _
"DefaultDir=;" & _
"Uid=;Pwd=;"

Public Function EstablishConnection()
On Error GoTo Err_Handler

Set objConn = New ADODB.Connection
objConn.ConnectionString = dbConnectionString
objConn.Open

Done:
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done

End Function

Public Function ReleaseConnection()
On Error GoTo Err_Handler

objConn.Close
Set objConn = Nothing

Done:
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done

End Function
[/vbcode]


Just fill in the missing pieces like the path of your mdb. Your connection to the database should not be maintained..otherwise your application will suffer in performance. The ideal method is to connect and capture (recordsets, queries) only when you most need to.

To establish your connection simply make a call to:

Call EstablishConnection

To release the connection simply make a call to:

Call ReleaseConnection

This way your objects are cleaned up.
The functions will use the constant defined as the connection string. You could also enhance this by creating a Class and using an object of that class to make connections to that db.

This one's for anyone to use ... including our good grumpy friend Raskew (still laffin).

 

[jaytheguru]

Afternoon all,

Firstly thank you mission2Java for sharing a wealthy information with us.

I have a question regarding the code you have provided. When used, VB.net(2005) says:

End Function
~~~~~~~~
Function Establised Connection doesn't return a value on all code paths.A null exception could occur at runtime when the result is used.

I am not sure how to rectify this problem.

Please Help!

Many thanks

Regards

Jay

 

[DJkarl]

Afternoon all,

Firstly thank you mission2Java for sharing a wealthy information with us.

I have a question regarding the code you have provided. When used, VB.net(2005) says:

End Function
~~~~~~~~
Function Establised Connection doesn't return a value on all code paths.A null exception could occur at runtime when the result is used.

I am not sure how to rectify this problem.

Please Help!

Many thanks

Regards

Jay

.NET is quite different from VBA and VB6, you could resolve this by adding the line

Return ""

to your code. Or you can change your function to a sub and that should take care of it as well.

 

[jaytheguru]

Oh ya! how could I forget that.

I'll try it on tomorrow.

many thanks

Regards

Jay