Visual Basic :: Error In Transferring From Excel To Access Database

Transferring VB Project From Access Database To SQL Server Database
Hello,

I have been working on a project at my employer to bring a current program that is run by a third-party company, in-house so that we can run it ourselves instead of paying out monthly fees to the third-party company.

The basics of the program are fairly simple. However, after getting the code about 90-95% done, I am realizing that my Access database will quickly become too small to use. I know we have SQL Server on at least one of our servers, and am thinking about trying to use that instead of Access.

My question is, how hard would it be to transfer my project to a SQL Server database instead of an Access database? I am using ADO to connect to my Access database right now. Would I have to make any adjustments to my VB code? I realize some of the SQL in the code will need to be adjusted, but what about my cn. and rs. lines of code (connections and recordsets)?

Shame on me for not realizing Access's size limitations before now. Any information that anyone can provide is greatly appreciated. Thank you in advance.

Transferring Excel To Access
Hello all
I have to populate an access db with data from a spreadsheet (as is, no specific columns etc) with VB6. I'm playing with DoCmd.TransferSpreadsheet, but are there better ways, and how do I get it working? I'm stuck with runtime error 2495, 'action or method requires a Table Name argument'.

This is my statement:
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, _
"table1", _
"c:my documents able1.xls", _
False

Help or new directions would be apreciated!

Transferring Data From Access Into Excel
hello,

i'v just been given an assignment of linking a database to other microsoft applications using visual basic. I have only had two lessions on visual basic and have only got as far as clicking on a button and having the date entered. I can programe in C and C++ builder but never used visual basic and it looks quite diffrent.

What im trying to do is transfer numbers from my database into an excel worksheet. My database is a hotel system and in the billing section the amount for each department is entered. Im trying to get these numbers to be transferd into a pre-made excel template to create an invoice. The idea is the formulas are already in the excel invoice and will automatically calculate the total and vat etc.

iv tried looking at previous posts but do not understand all of the code.

Any help would be much appreciated.

Thanks alot,
Jason

Transferring Data From Access To Excel
I'm using office 97 and am tryin to get some data from access to excel. With the code I've written in excel all that is displayed in the label is the default caption. How can I display what is in varVariable? Thanks for any help.

This is my code:

Option Explicit

Sub Data()
lblDat.Caption = varVariable()
End Sub

Function varVariable() As Variant

Dim dbsNozztst As Database
Dim rstLngBinDat As Recordset
Const conChunksize As Long = 32768

'open database "db1.mdb" and recordset "dbt_Pruefbericht"
Set dbsNozztst = OpenDatabase("C:db1.mdb")
Set rstLngBinDat = dbsNozztst.OpenRecordset("dbt_Pruefbericht")

'use GetChunk method to assign long binary data to a variable
varVariable = rstLngBinDat!dbf_MessDaten.GetChunk(0, conChunksize)

'close database and recordsets
dbsNozztst.Close

End Function

Transferring Data From Excel To Access
Guys,

I've been fiddling with VB for a week or so now and am learning fast, but I've hit a wall.

I need to take the content of two ranges (on speperate excel sheeets) and send them to existing tables in an existing database. Manually I would use the "Data, Convert To MSAccess..." menu option.

Any ideas?

Kindest

Frank

Transferring Data From Access To Excel
how can i transfer the data present in an Access Database to an Excel Spreadsheet??

done thru Visual Basic that is... any idea anyone?

Transferring Date From Access To Excel
I'm using office 97 and am tryin to get some data from access to excel. With the code I've written in excel all that is displayed in the label is the default caption. How can I display what is in varVariable? Thanks for any help.

This is my code:

Option Explicit

Sub Data()
lblDat.Caption = varVariable()
End Sub

Function varVariable() As Variant

Dim dbsNozztst As Database
Dim rstLngBinDat As Recordset
Const conChunksize As Long = 32768

'open database "db1.mdb" and recordset "dbt_Pruefbericht"
Set dbsNozztst = OpenDatabase("C:db1.mdb")
Set rstLngBinDat = dbsNozztst.OpenRecordset("dbt_Pruefbericht")

'use GetChunk method to assign long binary data to a variable
varVariable = rstLngBinDat!dbf_MessDaten.GetChunk(0, conChunksize)

'close database and recordsets
dbsNozztst.Close

End Function

Comparing Dates Before Transferring Data From Access DB To Excel (ADO)
I'm guess I need to transfer the info into a buffer (recordset?), do the comparison, then only extract the relevant data to Excel? Is there a simpler way of doing this? And does anyone have links to examples?

Must the Format be identical (eg. Long Date vs Long Date, or can it be like Long Date vs Short Date) when it comes to comparing dates?

Furthermore, when i use the preset MM/DD/YYYY, whenever i enter a date like '10/12/2006' (12 Oct), it automatically reverts to '12/10/2006'... and if i enter 12/15/2006' (15 Dec), it doesn't seem to recognise the date at all! (btw, i'm using British settings for win xp)

Thanks in advance... need all the help i can get.

Transferring Database With VB
OK, I made a basic login screen and and menu for a program, i added the database along with it, and I think i just found out why, but I make it able to register etc, i made a zip and put the database file, and the exe to my program, but when my friend said it dont work. any ideas or help

»Zero.

Transferring Data From One Database To Another
I have an Access 2000 database that has different table names and field names compared with another Access 2000 database.
I need to transfer data from this one database to the other but the tables and fields are different.

Is it possible to export data from one database to another if the tables are different and the fields are different?

Also how do I export the data from this one database to another??

Transferring Data From One .mdb Database To Another
I keep getting a run-time error '424', object required error for the following.

If rst.Fields("VAN") = Null And srt.Fields("VAN") Is Not Null Then MsgBox "Transferring Van #": rst.Fields("VAN") = srt.Fields("VAN")

Basically rst and srt are both ADODB recordsets tied to different access databases. They both have similar fields to convert the data from and all have the same data types.

My situation is that on one database if a Van number has been entered in srt - I do not want it erased by the Van number in rst. I'ved tried also:

If rst.Fields("VAN") = Null and srt.Fields("VAN") <> "" Then MsgBox "Transferring Van#": rst.Fields("VAN") = srt.Fields("VAN")

But it still will not transfer the data.

Please help.

Thanks

Sincerely,

Stephen

Transferring Records From One Database To Another
I need to transfer data from one database to another, using odbc connection.

Need Help With Transferring Excel To VB!
Hello!

Let me first start off by saying that I have just started using Visual Basic, so I'm pretty much a novice, so hopefully my question doesn't sound too stupid..

Ok..so I was thinking of making an application with VB that can take data from certain Excel spreadsheet cells(not many, just a few cells) and populate them into a VB application that I will create and they would appear in their proper fields..I'm having problems of conceptualizing how to go about this.I tried to think of ways to do this, but none make sense.If anyone out there can help me on how to possibly go about doing this(if it is in fact possible), it would be most appreciated. Thank you!

Transferring Data In Excel
I want to put some code behind a button that when pressed will copy the rows that i have highlighted and transfer them into a new worksheet.can anyone help?

Transferring Data To Excel
I am writing a program which transfers data from the serial port to an Excel file.

At the moment, values are read into a label every 5 seconds. Each new value overwrites the previous one so there is only one value in the label at a time.
Below is the code which does this.

Code:
Private Sub Timer1_Timer()
' Read in and display values.
Dim in_byte As String
Dim in_message As String
in_message = ""
MSComm1.InputLen = 1 ' Read a single byte from buffer each time.
MSComm1.Output = "READ?" & Chr(13) & Chr(10) ' Query instrument.
Do
Do
DoEvents ' Continue processing events.
Loop Until MSComm1.InBufferCount >= 1 ' Wait for a character.
in_byte = MSComm1.Input
in_message = in_message & in_byte ' Append to i/p string.
Loop Until (in_byte = Chr(10)) ' Wait for a LF terminator.
Label2.Caption = in_message ' Display message.
End Sub

I now need to send these values to an Excel file starting at cell B3. The times must start at cell A3, the first time being 0 and incrementing in steps of five.
The reason that they must start here is that later i will be inserting headings in the range A1 to B2 with a VB macro.

I already have a program which creates a two dimensional array using a random number generator and sends it to an Excel file.
The following code does this.

Code:
Private Sub Form_Load()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng As Excel.Range

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")

' Get a new workbook.
Set oWB = oXL.Workbooks.Add
Set oWS = oWB.ActiveSheet

' Initialize the random number generator.
Randomize

' Create an array with 100 rows and 2 columns.
Dim DataArray(1 To 100, 1 To 2) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = r - 1
DataArray(r, 2) = Rnd() * 1000
Next

' Transfer the array to the worksheet starting at cell A3.
Range("A3").Resize(100, 2).Value = DataArray

'Save the Workbook and Quit Excel
oWB.SaveAs "C:Book1.xls"
oXL.Quit

What i need to do is replace the random number generator with the values from the serial port and there corresponding times.

The program must be fully automated and be able to cater for an unknown number of values.

Can anyone help?

Transferring Data From Excel To VB
Ok..so I was thinking of making an application with VB that can take data from certain Excel spreadsheet cells(not many, just a few cells) and populate them into a VB application that I will create and they would appear in their proper fields..I'm having problems of conceptualizing how to go about this.I tried to think of ways to do this, but none make sense.If anyone out there can help me on how to possibly go about doing this(if it is in fact possible), it would be most appreciated. Thank you!

ps. What I want to do is lets say take the value in cell A4 and that value should appear in a specified text box in my application with a push of a command button that send it from excel to the app.

Transferring Data Via VBA In Excel--Can It Be Done?
HI,

I am working on a spec for my team and their request at the time seemed somewhat simple--until I got to this part! The idea is that there are three spreads in this workbook: Credit, Investment and a report Shadow. (The report shadow will be used to compare their own data with Regional data to make sure nothing is missing.) Ideally they will fill out the Credit spread and when a loan books (or date closes) and they insert the closing date, the third spread will be activated and filter in certain information such as ClientName, DateClosed, etc. So this "shadow report" should be populated ONLY if the booking date (that one column) is posted with no duplicates. Right now I have it set up to be run by a macro. Not ideal, but it works, but it only grabs the first line and I worry about duplicate information.

Is this even possible with VBA? And if this is beyond the scope of VBA, what language does this?

Thanks for any advice!!

Sarah

P.S. I have attached my file in case it helps to see what I am "trying" to program.

Calling An Excel Macro From An Access Database That Refers To That Database
hi

i have a macro in excel that inserts some values into a table in a database in access, but i want to make it so i dont have to touch the excel file, instead i have a module in the access file that calls the sub in the excel file, but then i get an error saying that the database is already open....is there a way of getting around this?

thanks

jimmyp

Transferring Data Between Excel Worksheets
I am creating a mini system where a user can highlight a number of cells and then on the click of a button the data in these cells is transferred to designated cells in another worksheet. Any ideas on how to code this?

Thanx

Transferring Data From Excel To Word
I have given up on trying to transfer data straight from Excel to Word. So, what I will do is attempt to write the data to a text file (from Excel), go to Word and extract the data from the Textfile and insert it into my Document using bookmarks.

So here are my questions:
1. How do I transfer my data from an Excel file to a textfile.
2. How do I transfer the data from the text file into Word.

I am trying to do this on my own in the meantime and if I manage to do it I will post it for the benefit of other forum members.

Transferring From Excel To Standalone Projects
Hi.

I've only just made the transfer from Visual Script (which has proved useful over the years with Telnet,) to Visual Basic 6, and I have a some questions. Actually I only have two, and I've forgotten one of them, so after that preamble, here goes:

Most of my work so far has been written under Excel (because I have access to that at work, but VB is my copy and so at home only,) and although they import to VB well enough all the forms are called "Designer Forms" and I am unable to modify them with such things as File/Edit bars & minimise/maximise buttons.

Any help please?

Thanks.

Transferring Variables From Word To Excel
I am a self-taught VB6 “newbie”. I developed a Word template that uses forms for our marketing people to write purchase and sales contracts. On the forms, the user enters data, selects from drop-down lists, and checks boxes. The template then selects the proper contract language and creates the contract.

I would like to transfer the data that has been entered by the user to a spreadsheet that would maintain the basic information for all of our contracts. Can anyone guide me through how to use VB6 to append a set of variables from a Word VB6 program to an Excel spreadsheet?

I don’t know if it helps, but I have figured out how to assign the data I want to transfer to document variables. All I need to do from there is figure out how to append the set of document variables as a new row in a spreadsheet.

MUCH thanks in advance!

Vba Excel -sorting Then Transferring Then Looping!
Hey guys.
Im comfortable enough with VB but know nothing about VBA.Im trying to write a macro in Excel that does the following.

*First I want to sort column E descending.
*I then want to transfer the values E1 to a new sheet into column (E1 into)A1
*I then want to move on to column F, sort descending again, then I want to transfer the value of F1 to a new sheet into column (F1 into)A2
*And so on and so forth

below is very rough pseudocode.to be honest Im looking for direction with syntax/the best functions to use above all else.any help would be greatly appreciated.

Code:
Range1 = E:L 'this will be variable in the future done through input box or something
For Each column in Range

column.sort

for i= 1 to 8
Sheets("Sheet1").Select
MyValue = Range("E").Offset(0, i).Value
Sheets("Sheet2").Select
Range("A").Offset(i, 0).Value = MyValue
next
Next

thanks.

Transferring Data From Excel Into VB6 Combobox
I am locating a sheet inside of Excel(I've got this), but when I try to take the data from this sheet, I am getting an error. (Object doesn't support this property or method) I have referenced the Excel Library, but can't quite seem to figure this one out. I would really appreciate the help.

objExcel.Range("A9").Select (Works)
objExcel.Range(objExcel.Selection, objExcel.Selection.End(xlDown)).Select (Works)
Set Item = objExcel.ActiveCell.Selection (This is where my error is)

For Each Item In Selection
Item.Activate
If IsEmpty(Item) Then
GoTo Again
End If
cboPcMark.AddItem Item
Again:
Next Item
End If

Transferring Data From 1 Excel Spreadsheet To Another
Can anybody out there please give me some advice in regards to transferring data from one spreadsheet to another?

much obilged

Transferring Data From Word To Excel
How to read the data from msword file one by one........and transfer it to excel. I can transfer the data to excel but the problem I am facing is reading the msword file.......if anyone can redirect me to some older messeges similar to this or give me a code will be quite helpful to me..........
Tx in advance

Transferring A Column Heading From VB To Excel
I have picked up a nearly complete application which has been written in VB6. It will export data from Access database tables to a variety of different applications, one of which is Excel. I need to carry over the name of the fields to create the column headings in Excel - I am sure this can be done I just don't know how.

Any help would be gratefully received.

Transferring Data From One Sheet To Another In EXCEL
Hi,

I am having trouble transferring data from one EXCEL sheet to another, but without using the copy function. I need to put the copied data into a relative cell reference, rather than absolute cell reference.

Currently, the code which won't work is:

Code:Worksheets("Sheet3").ActiveCell.Value = Worksheets("Sheet4").ActiveCell.Value

I think you can only have one ActiveCell in a Workbook, so therefore, how do I transfer the data from the ActiveCell to the other Worksheet using a relative cell reference.

I'd be stoked with any assistance.

Xav6

Transferring Data In Access/VB6
I am writing an app that processes fitness event results (running races, etc) and I have a 'raw' access database with a single table that collects data from my timer. I want to transfer that data periodically to another access database, or another table within the same access database, to make changes and still preserve the data in the original table. I need to do this in vb6 code. I am ok writing and reading the data. I just need some advice on the best way to get data from the original to the 'edit' table/db to make my changes.

Any suggestions?

Transferring Data From Excel To Visual Basic
I have some data in excel sheet and I want to use that data in visual basic..How can I make visaul basic to read that data in excel? And after reading data, how can I input result to a specific cell in that excel sheet?

Thanx for help!!

Transferring Tables From Access To Web Server
Could anyone please help me with this ???

I am creating an application to transfer data from users's PC's via a default internet connection (using FTP) to a web server.
Here is the scenario, Each user has a Access2000 DB on there PC's (72 users) each DB contains a table called stock. The master DB on the web server needs to contain 72 tables called user1stock, user2stock, user3stock, etc so that each user doesnt overwrite the stock file in the master DB. I want my application to automatically, when run from each user export and rename the table and then the bit I cannot work out is how do I get the renamed tables into my master DB on the server ??

Can anyone please help ?????

Regards

Transferring Image Between Access Field And VB
Hi

I'm having problem linking an image in an access database to my form. The image is a company logo which I need to be able to update if the company logo changes at any time. When the form loads up I get this message....

"Unable to bind to field or Datamember: 'Logo' "

What type of field should the Logo image be in the access database? OLE object? And should I use the 'Image' control on the VB form or something else?

Is there restrictions on what file types I can use for this logo so that it can be transferred between VB and access?

Also.
Using the Common Dialog Control im allowing the user to select a new image if desired. How do I then associate this picture to the database field (i know how to display the image inside the image control on the form already).

This is what i have in the Form_unload procedure

Private Sub Form_Unload(Cancel As Integer)
Data1.Recordset.Update "Logo", imgLogo.Picture
Data1.Recordset.Update "CompanyName",
txtCompanyName.Text
Data1.Recordset.Update "SupportPhone",
txtSupportPhone.Text
Data1.Recordset.Update "SupportPerson",
txtSupportPerson.Text
Data1.Recordset.Update "SupportCompany",
txtSupportCompany.Text
Data1.Recordset.Update "BackColor", txtBackColour.Text
Data1.Recordset.Update "FontColor", txtForeColour.Text
End Sub

I'm sure i've asked enough quesitons for one day.

Thanx for ANY help you can offer.

Clint

Automatically Transferring Outlook Info Into Access
I would like to transfer an Outlook 2000 Event Calendar information into an Access 2000 database. Please advise if and how I can do this where after something is entered in the Event Calendar it will also show up in an Access 2000 database. I was hoping this can be done automatically. Or if that cant be done, then run something once every 10 mins to update the Access database with the any new Outlook information?

Please advise.

Error Handling With Transferring Err.Number And Err.Description To A Module
I have the following in an Access module (of which I could also have in a Visual Basic program module):

Code:
Option Compare Database
Option Explicit

Public lngError As Long
Public strError As String

'Any error in the program saves it in a textfile!
Public Sub Error_Action(g_sErr As Long, g_sErrDesc As String, _
g_sErrorwhere As String)
On Error GoTo Err_Error_Action

SysCmd acSysCmdSetStatus, "Catching an Error #" & g_sErr & " - " & _
g_sErrDesc & " @ coding module - " & g_sErrorwhere & "..."
Dim g_lFile As Long
Dim e_Path As String
'declares g_lFile as freefile to append and load all above listed textfiles.
g_lFile = FreeFile
Set Db = Nothing
Set Db = CurrentDb()
SysCmd acSysCmdSetStatus, "Saving Error #" & g_sErr & " - " & _
g_sErrDesc & "... to file: I:dataACCESSDataMainerror.log"
e_Path = "I:dataACCESSDataMain"

save_file:
Open e_Path & "error.log" For Append As #g_lFile
Print #1, Err; "# "; Err.Description; " location: "; g_sErrorwhere; " ";
Print #1, Time; ","; Date; " "; Db.Name; " - caused by: "; fOSUserName; "on CPU: "; fOSMachineName
Print #1, ""
Close #1
SysCmd acSysCmdClearStatus
Set Db = Nothing

Exit_Error:
On Error Resume Next
Exit Sub

Err_Error_Action:
Close #1
If Err = 58 Then
If Len(Dir("C:EC", vbDirectory Or vbHidden Or vbReadOnly Or vbSystem)) = 0 Then
MkDir ("C:EC")
End If
If Len(Dir("C:ECdata", vbDirectory Or vbHidden Or vbReadOnly Or vbSystem)) = 0 Then
MkDir ("C:ECdata")
End If
If Len(Dir("C:ECdataACCESS", vbDirectory Or vbHidden Or vbReadOnly Or vbSystem)) = 0 Then
MkDir ("C:ECdataACCESS")
End If
If Len(Dir("C:ECdataACCESSDataMain", vbDirectory Or vbHidden_
Or vbReadOnly Or vbSystem)) = 0 Then
MkDir ("C:ECdataACCESSDataMain")
End If
e_Path = "C:dataACCESSDataMain"
GoTo save_file
End If
Resume Exit_Error

End Sub

Thus when an error occurs in another form procedure I have the following:

Code:
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer

Exit_Form_Timer:
On Error Resume Next
Exit Sub

Err_Form_Timer:

lngError = Err.Number
strError = Err.Description
Call Error_Action(lngError, strError, "frmLogMonitor @ Form_Timer")
Resume Exit_Form_Timer
End Sub

My problem is that when I view my textfile I see a 0 for the Error Number and there is no description shown in strError but only the place of where the error occurs shows up! It's like its not transferring the Error Information over into the Error_Action module and originally it did but now it stop showing. Is there anyway I can correct this?

Creating New Word Document And Transferring Data Fields From Access
Hi

I'm trying to find a way to start up a New Word document whilst in Access, where it will transfer particular data fields to the new word document....can this be done, if so, can anyone please help?!!

Many thanks

Matt Davis

Excel, Access Database Help
Hey All,

Newbie on excel/databases any help would be great thanks

ok I have database with "Items" on it which has two fields in it (ItemName,HowManySold), and I would like it so that when I click a button it displays this data in an excel sheet in 2 colums down the page.

ItemName HowManySold
Shirt 5
Pants 6
etc.

After which I would like to be able to produce a graph/table on it in excel, but i am guessing i need to add the data to excel first.
I have the following code at present but it does not display it down a page, it just shows one item!.

Code:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Worksheets.Add

'Fill cells using recordset

Do Until rs.EOF

xlWS.Range("A1").Value = rs("ItemName")
xlWS.Range("B1").Value = rs("HowManySold")

rs.MoveNext

loop

' save spreadsheet
xlWS.SaveAs "mysheet.xls"
xlApp.Quit

' Free memory
Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

End Sub

Creating An MS Access Database From Within Excel
Hi All

Is it possible to create an MS Access database from within MS Excel VBA?

I have so far only been able to start another instance of the MS Access application. I now want to be able to :-

1. Create a blank general database
2. Create a blank table with 'n' number of fields of "variant" datatypes
3. Populate this table with data from an already populated Excel VBA array, hence the need for the fields in the table to be of variant datatype! Can this entire array be dumped in to the table in one go or does this need to be done field by field on a record by record basis?
4. Sort this table on 'm' ('m' obviously <= 'n') number of fields in the independently specified sort order for the 'm' sort fields, preferably using appropriate SQL statement to sort the table/query.
5. Repopulate the array with the sorted data. Does this also needs to be done on dataitem by dataitem or can the whole table be dumped back in to the array?

The reason I want to go this path is because of the high degree of probability of having to work with more 65536 rows of data with the need to sort the same on more that 3 fields on independently specified sort order for each of the sort fields - hence would not ordinarily be able to do it on an Excel worksheet or array sorting using quicksort sorting on 3 or 4 dimensions (columns) - the code that I got given by Carl Porch (refer to one of his older posts on this forum so far only works for me for sorting on independent sort order on upto 2 dimensions, I am currently working on this with his assistance to be able to do it on 3 & 4 dimensions).

The other reason of going this way is that I can then have a highly generic "modus operandi" for sorting a dataset on any number of fields on independently specified sort orders for each of the sort fields instead of having specific codes for sorting on 'x' number of fields.

Any assistance shall be highly valued.

Best regards

Deepak Agarwal

Searching An Access Database From Excel
Hello all,
I have yet another problem. I would like to automate Access from within an Excel workbook. I want to use an Excel userform to search an Access table, and fill in values within that Access database. I found this generic code on http://www.ExcelTip.com/:

Code:
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:FolderNameDataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("A" & r).Value
.Fields("FirstName") = Range("B" & r).Value
.Fields("MI") = Range("C" & r).Value
.Fields("LastName") = Range("D" & r).Value
.Fields("Prefix") = Range("E" & r).Value
.Fields("Suffix") = Range("F" & r).Value
.Fields("MaintCo") = Range("G" & r).Value
.Fields("Installer") = Range("H" & r).Value
.Fields("Distributor") = Range("I" & r).Value
.Fields("Manufacturer") = Range("J" & r).Value
.Fields("Agency") = Range("K" & r).Value
.Fields("ContractDue") = Range("L" & r).Value
.Fields("StartDate") = Range("M" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

What that code does is copy information from an Excel worksheet to an Access table, but I am not sure how to search an Access database, or how to fill in the information in the Access database from an Excel userform.

I am perfectly willing to read over the basics of automating Access within Excel's VB capabilities, but I have not really been able to find any helpful web sites on the subject.

The reason I am asking about this is:

We are generating contracts through an Excel workbook. The first worksheet of the workbook is an input sheet, which fills in all of the pertinent information throughout the rest of the workbook (the body of the contract). We also use an Access database to track our contracts, so that we can schedule inspections, etc. I would like to give users the option of adding a record to our Access database when they finish generating the contract. We assign a job number to each client, so the user would enter the job number in an Excel userform. Then, I want Excel to search our Access database (all of the information is stored in an Access table) for that job number. If the job number exists, then Excel would initialize one userform to update the Access database record. If the job number does not exist, then Excel would initialize a different userform to add a record to our Access database.

I know that this is a very long post, and that I am asking an awful lot, but really all I am asking (at the moment) is that someone point me in a direction toward a free web site that will give me some tutorials on automating Access from within Excel VBA.

Access Database To Excel Spreadsheet
I need to convert my database to spreadsheet format in VB code - is there an easy way to do this or do I have to write a CSV file and then read it into excel?

Excel Data To Access Database
Hi!

I will do a program that will get the data from an excel file and it will be loaded to Access database. Do you have any idea on how to do it? If yes, please advise me ASAP.

Thanks a lot!!!

Update Access Database Using Excel
Can anybody please help / let me know whether this is possible?

I have an excel spreadsheet that I use to update a table in an access database (using ADO)

Sample code attached
Code:
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

    ' connect to the database

Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:xxxxxxxxxx.mdb;"

    ' open a recordset

    Set rs = New ADODB.Recordset
    rs.Open "xxxxxx", cn, adOpenKeyset, adLockOptimistic, adCmdTable

    r = 1 ' the start row in the worksheet
    Do While Len(Range("B" & r).Formula) > 0
    ' repeat until first empty cell in column B
        With rs

            .AddNew

            ' add values to each field in the record

            .Fields("Ref") = Range("B" & r).Value
            .Fields("Number") = Range("C" & r).Value
            .Fields("Project") = Range("D" & r).Value
            .Fields("Manager") = Range("E" & r).Value
            .Fields("Date") = Range("F" & r).Value
            .Fields("RAG") = Range("G" & r).Value
            .Fields("Scope") = Range("H" & r).Value
            .Fields("Scope extra") = Range("I" & r).Value
            .Fields("Progress") = Range("J" & r).Value
            .Fields("Progress extra") = Range("K" & r).Value
            .Fields("Start Date") = Range("L" & r).Value
            .Fields("End Date") = Range("M" & r).Value
            .Fields("Current End Date") = Range("N" & r).Value
            .Fields("Phase") = Range("O" & r).Value
            .Fields("Original Budget") = Range("P" & r).Value
            .Fields("Approved Budget") = Range("Q" & r).Value
            .Fields("Forecast Budget") = Range("R" & r).Value
            .Fields("Author") = Range("S" & r).Value
            .Fields("Written Date") = Range("T" & r).Value
            .Fields("Sponsor") = Range("U" & r).Value

            ' add more fields if necessary...

            .Update ' stores the new record

        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

However, this writes a new record each time. I have defined the number as a primary key in the database but basically just want to provide the user with the ability to update an existing record (if it exists). I can give the user the choice on the input form and this will run either piece of code. The code I don't have is the update - help!!

Edited by - Geof on 6/19/2006 3:58:47 AM

Vb6 Access Database Error Please Help
When ever I do a sql delete statement on a table in an access database I get the error -2147217900 which in most cases would mean that the table was not found or there was an extra field something. The problem is that I have tried replacing the database with one I know work and I still get the error. It happens the first time the routine is hit and the table that it is happening too has been the same for at least 2 years. Several other customers have used the same disk to install and have had no problems at all. Here is the code that bombs it out
VB Code:
Dim strCn As StringDim strSql As String strCn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _ "DBQ=" & App.Path & ";" & _ "DefaultDir=" & App.Path & ";" & _ "Uid=Admin;Pwd=;" strSql = "DELETE FROM [" & FileName & "] IN '" & dbLocation & "'" frmftp.adoCn.Execute strSql Do While stillexecuting Loop Dim a 'Append data to Access table. stillexecuting = True strSql = "INSERT INTO [" & FileName & "] IN '" & dbLocation & "'" strSql = strSql & " SELECT * FROM " & TextFile frmftp.adoCn.Execute strSql, aExitSub:
I know that this has to be a driver issue or mdac. They are running the latest version of mdac so that shouldnt be it. I am at a loss of what else to try to make these computers work. Any suggestions please? I have also sent code in with debug stuff in so I know what values are in the fields at the time and everything matches to my machine.

Access Database, Error
Hi all

here is my code

VB Code:
SQL = "Select * from Student"Set MySearch = MyDatabase.OpenRecordset(SQL)MsgBox MySearch.RecordCount

The error occurs on the middle line. And the MySearch and MYDatabase are declared publicly in the global module.

"Object variable or With block variable not set"

Any ideas?

ILMV aka DamnAccessBoy

Database Access Error
Hi people., more errors using access.

This time its ..

"3061, Too few parameters, expected 1"

Heres my code

VB Code:
Private Sub cmdLogin_Click() If txtUserName.Text = "" Then Call MsgBox("Please enter a username", vbExclamation, "Login Error") Else SqLtXt = "SELECT Username, Password" SqLtXt = SqLtXt + " From Users" SqLtXt = SqLtXt + " WHERE Username = " & txtUserName.Text MsgBox SqLtXt Set MyUsers = MyDatabase.OpenRecordset(SqLtXt) ' <--- Error Occurs Here !!! Username = MyUsers!Username & "" Password = MyUsers!Password & "" If txtPassword.Text = Password Then 'Call CorrectPassword Else Call MsgBox("Incorrect Password", vbExclamation, "Login Error") End If End IfEnd Sub

Database Access Error
Ok, this is about to push me over the edge! My app was working fine, accessing the database w/o any problems... However, when I tried to make it so that the user would have to find the database, everything went straight to *well, you get the idea*.

Here's where I'm getting the error...

VB Code:
Set DB = New ADODB.ConnectionSet RS = New ADODB.Recordset[b]DB.open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & App.Path & "data.mdb; Jet OLEDB: Database Password=password"[/b]

I'm getting a

Could not find installable ISAM

error and it is very very very frustrating!

Can anyone help me out here

As I said, it was working perfectly b4, but now it is driving me batty! I even tried changing it back to exactly the way it was b4 and it still is giving me problems.

I do have DB and RS declared publiclike and I have ADODB 2.7 refrenced...

HELP!!!

Thank you ,

Squirrelly1

Error With A ACCESS Database
I am still learning databases in VB, (this being my first practical app using one!) and have hit a snag. The following code generates a Type Mismatch.

during the debug I get these values:

sViewQuery = qryCustomers (as it should)
rstData = nothing

This is (what I think is pertinent) the code:

Private sViewQuery As String
Private dDB As Database
Private rstData As Recordset

...

Set rstData = dDB.OpenRecordset(sViewQuery)

Any help would be greatly appreciated!!
:-)

&"Unrecognized Database Format&" Error After Compacting The Access Database.
Urgent..Need help.

I am using VB 5.0 with Access97 database. I am using the following code in vb5.0, to compact access 97 database. It compacts successfully.
But after compacting when I access that database, it throws and error message "unrecognized database format"

I am having "Microsoft scripting runtime", "Microsoft Jet and scripting replication objects 2.6" references added to the project.

Private Sub CompactMDB(MDBfilename As String) 'filename can include full path
Dim vJet As New JRO.JetEngine
Dim vBak As String
Dim vBackup As String
Dim vSource As String

'if backup already exists, kill it
vBak = Left(MDBfilename, InStr(MDBfilename, ".")) & "bak"

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(vBak) Then
Kill vBak
End If

Set fso = Nothing

'set datasource
vSource = "Data Source=" & MDBfilename & ";Jet OLEDBatabase Password="
vBackup = "Data Source=" & vBak & ";Jet OLEDBatabase Password=" 'you can change the password
'vSource = "Data Source=" & MDBfilename
'vBackup = "Data Source=" & vBak 'you can change the password

'compact/repair & backup
vJet.CompactDatabase vSource, vBackup 'compacts to a backup file

Kill MDBfilename 'kills original mdb
FileCopy vBak, MDBfilename 'copy bak to mdb
End Sub

Can't Open New MS Access Database Created Using Excel VBA With ADO's
Hi All

When creating new Access databases using Excel VBA using ADO why am I not able to open the newly created database in Access 97.

The code used to create the new database is shown below :-

Code:
Dim adoxCat As ADOX.Catalog
Set adoxCat = New ADOX.Catalog
adoxCat.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & Application.Path & "" & "TempDb.mdb';"

There is further code beyond to create table, its fields etc and populating the table etc. but at this stage that may not be relevant.

When trying to open this database, it comes up with the error "Unrecognised database format ...followed by the full path of the database"

Any suggestions!

Best regards

Deepak Agarwal