Can someone give me generic code to connect to a database (SQL) via VB code? My forum is too cramped and I want to know how to do it through code anyways.


~~ Tc

TC

Your question is too general for me to give you a good answer.

Most SQL-addressible databases required specific DLL files and connection strings. "Minor" changes to the string can make them inoperable. Therefore, giving you a "generic" connection string is useless. There are many sites on the internet that can provide you with connection strings for major databases, as well as listing, and supplying, the required files.

Your VB version is important. If, for example, you were planning to use VB3 (old technology) to connect to an Access 2002 database, you might have trouble because of the m_dac version. You should use at least version 2.5, preferrably 2.6. If you are going to use the MSJet driver then use MSJet version 4.0. If you need to upgrade your m_dac file, Microsoft has them available for download.

Well, Im using VB 6, and my m_dac version is 2.6. I was just thinking something along the lines of:


dim rs as new ADORS.Recordset
dim co as new ADORS.Command



rs.open "SELECT * FROM MyTable WHERE [User] LIKE '%Test%'",
"DNS=MyDatabase", adOpenKeySet, adLockOptimistic, adCmdTxt


If rs.RecordCount = 0 Then

MsgBox "Unable to find any users", vbCritical + vbOKOnly, "WARNING!"

End If


Would that work?

TC -

I use the following code snippets to connect VB to MSAccess tables locally and in networks. It uses ADO and the MSJet driver. I put it in the globals.bas file. You must add the ADO data objects reference to your project.

Public m_JETDir As String 'drive and directory of mdb file
Public m_JETmdb As String 'name and extension of mdb file
Public Xconx As ADODB.Connection
Public Xcmd As ADODB.Command
Public Xrs As ADODB.Recordset

-------------------------------------------------------
Sub JETCONX() 'make connection to MSAccess Database

Set Xconx = New ADODB.Connection
Set Xcmd = New ADODB.Command
Set Xrs = New ADODB.Recordset

Set Xconx = CreateObject("ADODB.Connection")
Xconx.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & m_JETDir & "\" & m_JETmdb
Set Xrs = CreateObject("ADODB.Recordset")
Xrs.CursorLocation = adUseServer

End Sub

-----------------------------------------------------------
Sub JETDROP() 'drop connection th MSAccess Database

Set Xconx = Nothing
Set Xcmd = Nothing
Set Xrs = Nothing

End Sub

-------------------------------------------------------------
If you are not sure how to use ADO, I strongly recommend checking:
msdn.microsoft.com/library/ <continue next line>
default.asp?url=/library/en-us/ado270/htm/ <continue next line>
mdmscimicrosoftadoprogrammersreference.asp

-------------------------------------------------------------
I compact and standardize my code with one module to handle inserts, one for deletes and one for updates. It makes commits, rollbacks, and error logging very easy. I pass only an SQL statement, which also simplifies calling stored procedures. Because of the time loss, I don't drop the connection until the application either errors-out or closes normally. Only recordsets are opened and closed.

Hello everyone, I'm new here, and sorry if I'm necroposting this thread, but I spent last 3 days to solve this problem and I need your help.

In this thread, it's mentioned a possible VB3 connection to a recent version of Access. Now I have a project in VB3 (I know it's time to migrate...) connecting to Access 2.0. Some tables have 700K records and it became very slow... So I thinked to migrate to Access 2000 or 2003.

How can I connect a recent version of Access in a VB3 project??
I have MSJet 4.0 and MDAC 2.7 installed.

Thanks in advice