1,105,384 Community Members

VB: Connect to Access database via ODBC datasource name

Member Avatar
ChristinaOrchid
Newbie Poster
5 posts since Jan 2003
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi Expert,

I already create a datasource name for my Access database. However, I failed to connect to that database using this code:

Set db = DBEngine.Workspaces(0).OpenDatabase("dbsourcename", ,"DSN=dbsourcename;UID=MyUID;PWD=Mypassw")

Could anyone help me please. Thank.

Member Avatar
Tekmaven
Software Architect
937 posts since Feb 2002
Reputation Points: 258 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 27 [?]
Team Colleague
 
0
 

What version of VB are you using? And, what is DBEngine declared as?

Member Avatar
aeinstein
Team Member - aka kaynine
643 posts since May 2002
Reputation Points: 79 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
Team Colleague
 
0
 

dont know much about VB @ all but let me ask u a coupla ???...

what db are u trying to access the Access DB from?

does it support ODBC via VB directly, or do you need connection services, managers &/or drivers?

is it necessary to establish a connection authorization from your destination db to your external source (ODBC) db PRIOR to attempting connection?

i come from the Lotus Notes/Domino world, whereby the answers to all of the above is a resounding YES, but i do not know if that is universally true.

Member Avatar
Dani
The Queen of DaniWeb
20,566 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 931 [?]
Skill Endorsements: 204 [?]
Administrator
Featured
Sponsor
 
0
 

Just wanna check up on something ...

Personally, I've never connected to a database via ODBC. However, just wanna check:

You say you used the command
Set db = DBEngine.Workspaces(0).OpenDatabase("dbsourcename", ,"DSN=dbsourcename;UID=MyUID;PWD=Mypassw")
to connect to the database. You *DID* replace the items in bold with your own variables, right? Just making sure! ;)

Also ... one more thing ... between dbsourcename and DSN=, you have two commas. Is it supposed to be like that. You use a ; to separate all of the other parameters.

Just a lil syntax check you may wanna look at!

Member Avatar
Tekmaven
Software Architect
937 posts since Feb 2002
Reputation Points: 258 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 27 [?]
Team Colleague
 
1
 

Dani -
Thats wrong :-P. When you use a DSN, you do not need to fill in any other information. The DSN stores everything for you.

Also you seperate connnectionstring items with a ;

Member Avatar
Dani
The Queen of DaniWeb
20,566 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 931 [?]
Skill Endorsements: 204 [?]
Administrator
Featured
Sponsor
 
0
 

What I'm saying is this is the code that ChristinaOrchid used. It doesn't matter whether they're using extraneous info, etc. It might be a syntax error (e.g. they used a comma instead of a semi-colon.)

You just said you don't need to fill in any other info? Well they did (as you can see). Maybe that's the problem?

Just trying to troubleshoot the code THEY used from a syntax error standpoint.

Member Avatar
Tekmaven
Software Architect
937 posts since Feb 2002
Reputation Points: 258 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 27 [?]
Team Colleague
 
1
 

If your using VB.NET or C#.NET the following link is your end-all resource!

http://www.connectionstrings.com/

The following is if you're using VB.NET:

Access
ODBC

Standard Security:

"Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;Uid=Admin;Pwd=asdasd;"

Workgroup:

"Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;SystemDB=\somepath\mydb.mdw;"

OLEDB, OleDbConnection (.NET)


Standard security:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=asdasd;"

Workgroup (system database):

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;","admin", ""

With password:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;","admin", ""

DSN

DSN:

"DSN=myDsn;Uid=username;Pwd=;"

File DSN:

"FILEDSN=c:\myData.dsn;Uid=username;Pwd=;"
Member Avatar
ChristinaOrchid
Newbie Poster
5 posts since Jan 2003
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi All,

I will try to describe more clearly my problem:
I am working on my Excel workbook and I create a button trying to connect to Access database or Oracle database that I already created datasource name for those 2 database in ODBC window. However, when I write the code like this:
Set db = DBEngine.Workspaces(0).OpenDatabase("", , , "ODBC;")
this open an ODBC window and let me select a datasource name. When I select an Oracle datasource name, the connection is successful. However, when I select an Access datasource name, I got the message error <<You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database. (Error 3423)>>

So, My first question is why I cannot access to Access database??

Second, I trie to test to connect to Oracle/Access database by passing the datasource name as follow:

Set db = DBEngine.Workspaces(0).OpenDatabase("Oracle7", , , "DSN=Oracle7; DBQ=sqn2_mars; UID=MyuserId; PWD=Mypwd"), the effect is the same as if my first example on the top.


Hi Tekmaven,
I did'nt declare my DbEngine. As I know DbEngine is the top level object in the DAO and we can use without declaring it.

Hi aeinstein,
My problem is when I trie to connect to Access database.

Hi cscgal,
I have compied my code and there is no error syntaxe that's why it open the ODBC window and let me select my datasource name.


Thank All for your response, I am looking for all your new response days and days :p)

ChristinaOrchid.

Member Avatar
ChristinaOrchid
Newbie Poster
5 posts since Jan 2003
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I forget to tell that I am using Microsoft Excel 97 .

Member Avatar
Tekmaven
Software Architect
937 posts since Feb 2002
Reputation Points: 258 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 27 [?]
Team Colleague
 
0
 

Huh? Are you tring to import data from a table? If you are, just selecet File->Open and change the file type to "Acess Databases." You will recieve a wizard that will automatticly import the data for you!

Member Avatar
ChristinaOrchid
Newbie Poster
5 posts since Jan 2003
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi Tekmaven,

First of all, I don't see the file type "Access database" in my Excel Worksheet.

Secondly, I don't want to access my database manually like you propose File/Open....etc.. I need to program it by clicking on a button, the user can recuperate datas in a table and display them on the Excel worksheet.

Member Avatar
Tekmaven
Software Architect
937 posts since Feb 2002
Reputation Points: 258 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 27 [?]
Team Colleague
 
0
 

Then why are you using Excel? Why not just use Access? You can accomplish this by creating a "Form".

Member Avatar
ChristinaOrchid
Newbie Poster
5 posts since Jan 2003
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi Tekmaven,

Because I have a lots of calculation in my project and Excel is a good tool and easy way for me to do it :p)

Member Avatar
samaru
a.k.a inscissor
1,255 posts since Feb 2002
Reputation Points: 145 [?]
Q&As Helped to Solve: 19 [?]
Skill Endorsements: 30 [?]
Team Colleague
 
0
 

Well, if you want to synchronize Access information from an Excel sheet (Which is what I think you want to do), you don't need any code. Synchronizing it with a sheet automatically sounds like a much better way to
go rather than a button that does it (unless you want to do call a vbscript, but it's not necessary in this case). A user can just go to the sheet and it'll be there. I've done this before, but not in Excel 7.0.

If you want to synchronize a sheet in your workbook with output from an ODBC datasource, go to the menu Data -> Get External Data -> New Database Query. If you don't have Microsoft Query, you might be asked to insert the CD and install this feature. If it all goes well, you should get a window where you get to pick a data source. Choose one you created and click ok. After that, you'll get another window where you can pick what columns you want. Select what columns and click on the -> button. Click next. The following dialogs are
self explanatory (sort by, etc.). The last dialog (Query Wizard - Finish) you can select "Return Data to Microsoft Excel." You can save the query if you want to by pressing on the button "Save Query." Click on finish.

After you do all that, you'll get a dialog that says "Returning External Data to Microsoft Excel." Select existing worksheet (assuming it's blank). Click ok. The information will be imported to your current sheet. The data is dynamic however. Any change you do in your ODBC database, it will update in Excel automatically. You can right click and hit Refresh. If you want you can right click on one of the fields and go to External Data Range Properties. There you can control the Refresh rate.

Member Avatar
samaru
a.k.a inscissor
1,255 posts since Feb 2002
Reputation Points: 145 [?]
Q&As Helped to Solve: 19 [?]
Skill Endorsements: 30 [?]
Team Colleague
 
0
 

I hope what you want is NOT a client in Excel to insert information into an Access database. There are better solutions than trying to do this, but something like that can be done.

Member Avatar
rmundkowsky
Newbie Poster
1 post since Nov 2003
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

This is probably a little too late for your project, but here is the answer (that was a pain to hack):

Set DB = WS.OpenDatabase("", False, False, "DSN=BLAH_NAME;SRVR=TCP/IP:blah_host.com:BLAH_SID;UID=BLAH_USER;PWD=BLAH_PASSWORD")

The false, false are what makes this work.

Hope that helps

Member Avatar
Paladine
Master Poster
812 posts since Feb 2003
Reputation Points: 138 [?]
Q&As Helped to Solve: 28 [?]
Skill Endorsements: 5 [?]
Team Colleague
 
0
 

Hmm, I am curious, what version of Access are you trying to connect to? You say you are using Excel 97, but what version of Access? And why are you using DAO to connect via ODBC instead of ADO ?

ADO would have made you life a whole lot simplier.

Member Avatar
vihang1
Newbie Poster
1 post since Mar 2004
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I am new to VB.Net and have a question. I am trying to connect VB.Net to Access. What I want to do is , when i select the database name, I want to popup the tables in the database...when one of the table is seleted, then the fields in the table should pop up...Can someone tell me how to go about it .

thanks in advance

Vihang

Member Avatar
yadi
Newbie Poster
3 posts since Jul 2004
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I’ve got a problem with this program.It only saves one data at time.What shall I do with it?
Private Sub cmdsave_Click()


Adodcprog.Recordset.MoveNext


And with this program I couldn’t search a data I wanted.Pls somebody help me!
End Sub
Private Sub cmdsearch_Click()
Dim stremployeeName As String
Dim strpaysheet As Variant
stremployeeName = InputBox("Please enter empid")
strpaysheet = datPrimaryRS.Recordset.Paysheet
datPrimaryRS.Recordset.Find "CompanyName Like " & _
Chr(39) & stremployeeName & "*" & Chr(39)
If datPrimaryRS.Recordset.EOF Then
datPrimaryRS.Recordset.Paysheet = strpaysheet
MsgBox stremployeeName & " not found."
End If
End Sub

Member Avatar
mjwest10
Light Poster
27 posts since Feb 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Check this out: Visual Basic Database Guide. It explains in detail how to connect to different databases (including Microsoft Access, SQL, Oracle). I think it should help you get a great understanding of VB and data access.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article