×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Connection to database

Connection to database

Connection to database

(OP)
Hi all,
this is and interesting and helpfull forum. I have red about sending (or passing) data from (or to) excel but I have not red anything about getting or sending data to a database (access). Is there any way to do it?. I Have been trying using DSN but it doesnt work fine. Is posible to use SQL instructions to insert data into an specific table?
Thanks for all.

RE: Connection to database

I don't see why you'd have a problem passing data to an Access table.  You'd need to create the Access object, open your database then open the recordset.  Once you have the recordset open, you simply add or edit the data.

I've only made VBA macros in Access rather than EB macros that access Access.  I haven't tested this, so it might not work.  It should point you in the right direction though.

CODE

Set oDB = CreateObject("ADODB.Connection")
oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Test.mdb"
Set oRS = oDB.OpenRecordset("tblTest")

RE: Connection to database

Actually, this FAQ sends data to Excel as if Excel were a DB. The sheets are seen as tables.

FAQ99-4068: How do I get data to Excel?

With Skie's advise above and this FAQ you should be able to get there pretty quickly.

calculus

RE: Connection to database

Create a blank database called DB.mdb in C:\Documents and Settings\All Users\Documents\Attachmate\Macros\Temp\

CODE

Sub Main()
   Const DBNAME = "C:\Documents and Settings\All Users\Documents\Attachmate\Macros\Temp\DB.mdb"
   Dim connection_string As String, cn As Object, cmd As Object
   
   connection_string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBNAME & ";Persist Security Info=False"

   Set cn = CreateObject("ADODB.Connection")
   Set cmd = CreateObject("ADODB.Command")

   cn.ConnectionString = connection_string
   cn.Open
   cmd.ActiveConnection = cn

   cmd.CommandText = "CREATE TABLE tblCustomers (ID text(16) CONSTRAINT pk Primary Key, PrimaryName text(26), SecondaryName text(26), Address1 text(23), Address2 text(22), City text(19), State text(2), Zip Integer);"
   cmd.Execute

   For i = 1 To 10
      strSQL = "insert into tblCustomers ( ID, PrimaryName, SecondaryName, Address1, Address2, City, State, Zip ) values ( '" & i & "', 'John', 'Dough', 'Address1', 'Address2', 'City', 'St', '00000' )"
      cmd.CommandText = strSQL
      cmd.Execute
   Next i
   
   Set cmd = Nothing
   Set cn = Nothing
End Sub

RE: Connection to database

(OP)
Thanks for all,
very useful informatation and most important...it works !!!
Thanks all again.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close