Connection to database

[alsiru]

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.

 

[Skie]

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.

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

 

[calculus]

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

faq99-4068

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

calculus

 

[WinblowsME]

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

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

 

[alsiru]

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