Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > connecting to a database


Reply
 
Thread Tools Display Modes
  #1  
Old 09-10-2004, 06:16 AM
dennis_89 dennis_89 is offline
Newcomer
 
Join Date: May 2004
Posts: 22
Question connecting to a database


I am wondering if there is a way to connect to a particular table within a database. Right now I have a vb program that connects to an access database that has one table in it, but I would like to connect it to a different database that has about 10 tables. Is there a way to specify the particular table you want the vb program to link to?

Would it sort of look like this?
Code:
 
Set db = ws.OpenDatabase(App.Path & "\database.mdb")
Set rs = db.OpenRecordset("Table", dbOpenTable)
Thanks,

Dennis
Reply With Quote
  #2  
Old 09-10-2004, 06:20 AM
Peperl's Avatar
Peperl Peperl is offline
Senior Contributor
 
Join Date: Apr 2003
Location: Cartagena, Spain
Posts: 1,371
Default

hi

I don't know esactly what do you refer. If you connect to a database, using the RecordSource property you can SELECT * FROM thetableyouneed to use, or selecting a couple of them at the same time if you need.
__________________
Pri Oh Persia
Reply With Quote
  #3  
Old 09-10-2004, 06:24 AM
dennis_89 dennis_89 is offline
Newcomer
 
Join Date: May 2004
Posts: 22
Default

I am trying to connect a visual basic program to an access database. It already works with the existing database, but I want to use a different database. What I am wondering is if the code I gave above will connect to a table in a database.

The reason I am asking this is because as I mentioned, it already works with an existing database, but when I export the table the program uses in the existing database, import it into the new database, change the name on the database that I want to connect to, I get an error saying the either the database or the table is not there, which is incorrect because they are there.

The 2 databases are in the same folder, so they paths are the same. I can't think another reason why this isn't working.
Reply With Quote
  #4  
Old 09-10-2004, 06:30 AM
Peperl's Avatar
Peperl Peperl is offline
Senior Contributor
 
Join Date: Apr 2003
Location: Cartagena, Spain
Posts: 1,371
Default

Yes, but you have written this

Quote:
Set db = ws.OpenDatabase(App.Path & "\database.mdb")
Set rs = db.OpenRecordset("Table", dbOpenTable)
if you say that this works in other connections MAY work with others databases. But what I asked you before is what objects are you using to connect? I mean, DB and RS how are they declared
__________________
Pri Oh Persia
Reply With Quote
  #5  
Old 09-10-2004, 06:34 AM
NEOLLE's Avatar
NEOLLE NEOLLE is offline
fully realized avatar

Super Moderator
* Expert *
 
Join Date: Jun 2004
Location: Davao Philippines
Posts: 2,295
Smile

I have tried this before. I only have one ADO connection and two Access Database files.

I established my connection on Database1. I placed Database2 in the same folder where my application is in.

Here is a sample Query Statement:

Database1 has a table named EMPLOYEES with fields (EMPLOYEE_NUMBER,FIRSTNAME,LASTNAME).

Database2 has a table named EMPLOYEES with fields (EMPLOYEE_NUMBER,FIRSTNAME,LASTNAME).


Code:
SELECT A.FIRSTNAME,B.LASTNAME
FROM    EMPLOYEES A, DATABASE2.EMPLOYEES B
WHERE  A.EMPLOYEE_NUMBER = B.EMPLOYEE_NUMBER
Reply With Quote
  #6  
Old 09-10-2004, 06:47 AM
Peperl's Avatar
Peperl Peperl is offline
Senior Contributor
 
Join Date: Apr 2003
Location: Cartagena, Spain
Posts: 1,371
Default

well .. I don't know If its possible to set two connections at once in the same queary, and maybe this is not an ellegant soluttion, but you can try to put two ADOs instead one and connect each of them to a different database, then make a bucle for comparing the values you are getting
__________________
Pri Oh Persia
Reply With Quote
  #7  
Old 09-10-2004, 06:50 AM
Demon Cleaner's Avatar
Demon Cleaner Demon Cleaner is offline
Centurion
 
Join Date: Mar 2004
Location: Sky Valley
Posts: 164
Default

Perpel is right. Better use ADO programming for this. If you haven't use ADO before, here is a place to start http://www.xtremevbtalk.com/showthre...threadid=66994
__________________
I Of The Storm
Reply With Quote
  #8  
Old 09-10-2004, 09:01 AM
MKoslof's Avatar
MKoslof MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Are you looking for a query that will span two databases. For this, you should use the Jet OLEDB provider. Then you can create one query to bring back data between multiple Jet databases, between Access and Excel, etc...since all use the same data provider.

Basically something like this:

Code:
'just as an example insert into this database from antother strInsert = "INSERT INTO mytable SELECT * FROM [MS Access;DATABASE=C:\Testing.mdb].[Clients]" 'execute into THIS active connection cn.execute strInsert
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->